Auto populating different Excel worksheets from main worksheet 2

[pitterpitter]

I have a main class roster listing all students in one column and which class they took (1-6) in another column. Then I have a separate worksheet of each of the 6 classes. I want to be able to take all students from each class listed in the main roster and automatically populate the corresponding worksheets with the students name, etc. My IF formula does this but leaves blank lines on the rows where the result is false. How do I eliminate the blank lines? Here is the formula. Thanks so much for your help. I haven't used Excel in a while and my mind has just gone blank.
=IF('Main Roster'!G9="X", 'Main Roster'!B9, "")

 

[kjv1611]

I'm not sure, but I'm thinking the only way you're going to be able to do what you're asking is either to possibly use some sort of variable in your forumla(s), or else program it in VBA.

However, I'm thinking that your whole scenerio sounds like it would fit best in an Access database instead of an Excel workbook. What your wanting to do is part of what a database is used for.

Then you could export worksheets from Access whenever you need to provide it to someone else, say via email.

--

"If to err is human, then I must be some kind of human!" -Me

 

[SkipVought]

Hi,

"I have a main class roster listing all students in one column and which class they took (1-6) in another column."

Please post an example of this data.

Can students be related to more than one class?

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[pitterpitter]

I'm not sure if students can be related to more than one class since and the person I'm creating this for is not in today. I don't know how to attach a file but this is sort of a sample of my main roster..thank you so much!


Last Name First Name Initial Class 1 Class2
1 test1 test2 po X
2 test3 test4 bl X
3 test5 test6 gh x
4 blah blah bb x
5 mouse mickey mm x
6 duck donald dd x
7 dog goofy gd x

 

[SkipVought]

Please use TGML Tage for clarity. Search this page for TGML

Your table design is poor and thewarts the features of Excel...
[tt]
Last Name First Name Initial Class1 Class2
test1 test2 po X
test3 test4 bl X
test5 test6 gh x
blah blah bb x
mouse mickey mm x
duck donald dd x
dog goofy gd x
[/tt]
Your Normalized table ought to look something like...
[tt]
Last Name First Name Initial Class
test1 test2 po 1
test3 test4 bl 2
test5 test6 gh 1
blah blah bb 2
mouse mickey mm 1
duck donald dd 1
dog goofy gd 1
[/tt]
Now turn on your Data > Filter > AutoFilter and SELECT a Class. VOLA

Do you REALLY need a separate sheet for each class? That, too, is a poor design.


Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[pitterpitter]

Thank you. The table was given to me that way and had a separate sheet for each class which I know it's poor design. Let me get some clarification from the trainer, as to what exactly she needs it to do. In the meantime I'll work with your redesign and will get back to you if I have/need more info. Thanks again, I really appreciate your help!!!

 

[SkipVought]

You can easily insert a QueryTable Data > Import External Data... on each sheet to extract each classes roster, if this is a requirement.

faq68-5829.

Or you could insert a Data > PivotTable & PivotChart... with Class in the Page Field.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[pitterpitter]

Thanks again for all your help!