• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User


  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

How to reference onto a new sheet and THEN SORT


New Member

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.




Excel Ninja
Staff member

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


=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