Link spreadsheets to auto populate class rosters from master registration

Hi, I am new to the forum and just a casual user of excel. I am trying to link fields from a master registration sheet to auto populate class rosters once I paste in data and assign the classes. I have tried searching online, but can't seem to find the best way to get this done. I have attached an example of what I am trying to do.

Thanks.

Is that what you want?

done with PowerQuery (Get&Transform)

if you add something to source table (registration) go to appropriate sheet then right click on green table and select refresh

Maybe something like this:

Registration tab: H2:

Please Login or Register  to view this content.

Clas Blue E: D3:

Please Login or Register  to view this content.

The second formula is array formula entered with Ctrl+Shift+Enter. Drag it in E column as well

Please Login or Register  to view this content.

Drag it in G and H

I've added a dropdown in E1 - Class Blue E. Therefore there is no need to create a new tab each time. You can simply change the dropdown value and the formulas will automatically update.

I am not sure whether that is what I am looking for. I have never used Get&Transform, so I will need to research it and the steps to implement it.

I am not sure whether that is what I am looking for. I have never used Get&Transform, so I will need to research it and the steps to implement it.

As I said, there is a dropdown in the second tab, if you select it from there you have multiple choices. if you select another value, all of them will automatically be updated. but if you need new tabs for each class, then copy the formulas and over and change the references to the new sheet. also, first two formulas are array, so, after copying them over and changing the references you have to enter them with CTRL+SHIFT+ENTER instead of regular enter.

In fact, add the INDEX formula in D3, change it, pres CTRL+SHIFT+ENTER, drag it in column E as well and down. It should automaticaly change the ranges. the same with the Lookup formula, add it in G3, press ENTER, drag it to right and down.

I am having a problem with the duplicated names, i.e., Sue, not being populated on the class roster. Is there a way to fix this? Thanks!

Do your class names have to have a separate letter after it? (i.e. blue e, yellow g) and do you have the same name classes with different letters (i.e. blue g, blue e, blue a)?

If your class names don't have to have a separate letter (different field) then this solution may work for you.

The first thing I did was to create a name variable (Formulas>Name Manager) to count how many students you had registered. I called this variable StudentData.

Formula:

Please Login or Register  to view this content.

The next thing I did was to create an ID field (I made up IDs of course) to eliminate the duplicate name factor (no students will have the same ID number right?) and assigned IDs to each student. I did this on the registration tab (and added the columns on the the class tabs).

Following this, I used this array formula (enter with cntrl-shift-enter and then copy down) to bring in the students assigned to the different color classes (on the class tabs).

Formula:

Please Login or Register  to view this content.

In the first INDEX statement with the StudentData variable, the 1 is the first column (on the Registration tab) - that is the ID number and that is what I pull into the class tabs. I match the color of the class (7th column is equal to Blue, Green, Yellow, whatever - you can also make this a cell reference by having a cell with your class color in it). You will see the StudentData variable used again after this... make sure it is also referring to column 1 (the ID). The b$2:b2 reference needs to be the column you are bringing the ID into and needs to be the row above the first row where you want the first ID entered (in this case, the first ID is in column B and row 3).

I then used the INDEX/MATCH combination to match the ID number to the first name, last name, grade, etc...

Formula:

Please Login or Register  to view this content.

Registration!B:B is the first name, $B3 is the ID number to match and Registration!A:A is the ID number column you are matching to B3. You will notice the Registration!B:B changes depending on the data I want entered into the field.

I found it odd that your Allergies and Notes fields caused a 0 to be entered if there was nothing in them - thus you will noticed I added an IF statement to check for an empty field first and if there was one, to enter nothing ("") and if there was something there, to enter that data.

Also, there were a couple of class colors that had spaces after the word... those will have to be eliminated because a match counts every character and spaces count as a character.

I have attached the spreadsheet with these formulas in them... hope this will work for you.

Sorry, I need the class names to have separate letters. I have all my class rosters set up now, 39 total, so I don't want to go back and change everything. At this point, I will probably add periods to a name if I see a duplicates within the same class. Thanks for the suggestions though.

I’m glad you found a good solution. You should mark the thread solved now.