How to reference onto a new sheet and THEN SORT

[jilli]

Hi,


Does anyone know the best way to do thw following...


I want to have a master list (on 1ist worksheet) that plots a grid of people (rows) vs classes (columns). If the user adds a new person, they will add a new row with their name in the first column, then will put an X (or something) in each column for which they want to sign up for a class. So here's my question...I want to have a separate worksheet for each class. So say, for example, Sheet 2 represents one of the classes. I want to list out the people who have signed up (have Xs in the appropriate column for that class). I know how to use IF THEN to place the people's names on the new sheet IN THE SAME CORRESPONDING ROW as on the main sheet, but do NOT know how to group them so there aren't huge gaps between the names. Is it possible to do this with IF THEN. Or is there a better command. Or is this the funcationality of pivot tables? I'm obviously not well versed on how to approach.


Thanks!!!

Jilli

 

[Hui]

Jilli

I would add some helper columns off to the right of the Master list

Each Helper Column will have a value which will be the maximum value above it plus 1 where there is an X in the original class column

eg:

=if(upper(Class1)="X",Max(of the values above it)+1,0)

repeated for each class


This will leave you with a column of 0's and increasing numbers corresponding to where the students have put an X


You can now use the helper columns to locate your students


On the Class sheets, you will need a list of 1..100 etc down the left side and then use that value to retrieve the corresponding students etc

You won't be able to use a Vlookup but the use of Offset(Match()) will work well