Help with Excel Lists

[letty]

I have a huge guest list for a party. Some of the people have given us their emails and some have not. I want to create new sheets - 1 with the email people and 1 with the non email people. I know I can copy and paste, but I want the sheets to automatically update. So, if we get an email for someone, they are automatically moved to the email sheet and removed from the non-email sheet.

I've written and if then statement that transfers the names to the new sheet, but I can't figure out how to transfer only certain names. Do I use a pivot table and if so, how? Any advice would be great, thanks!

 

[CaptMurdock]

I have a huge guest list for a party. Some of the people have given us their emails and some have not. I want to create new sheets - 1 with the email people and 1 with the non email people. I know I can copy and paste, but I want the sheets to automatically update. So, if we get an email for someone, they are automatically moved to the email sheet and removed from the non-email sheet.

I've written and if then statement that transfers the names to the new sheet, but I can't figure out how to transfer only certain names. Do I use a pivot table and if so, how? Any advice would be great, thanks!

Have you tried Data > Filter, then c&p'ing the results?

 

[exegete77]

What version of Excel? If 2004, you could write simple VBA to achieve that.

If 2008, Pivot Table might work.

As an added thought, do you have last names and first names in separate cells? That makes things easier as well.

 

[peetah]

Create a sheet. Two columns. Name, email address
Create a 2nd sheet (called email address -- this is the one that has email addresses). Two columns called Name and email address. Get status of email address column from first sheet. If it isn't blank (meaning something is there), then then it copies value of name.

2nd sheet:
A1 contains: Name
B1 contains: Email Address
A2 contains: =IF(ISBLANK(Sheet1!B2),"",Sheet1!A2)
B2 contains: =IF(ISBLANK(Sheet1!B2),"",Sheet1!B2)

Copy contents of A2 and B2 of the 2nd sheet to as many rows as you need.

Look up the help for isblank to figure out how to make this work for names you don't have e-mail addresses for.

FWIW, I recommend using eventbrite. It'll help keep track of this stuff better.

Finally, poke around the help text of Excel. You'll find something that meets most of your needs.