• 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

    Hui...

  • 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

jilli

New Member
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

Excel Ninja
Staff member
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
 
Top