Excel - looking for subject and copying attending student

Hello,

I am stuck on a new project and cannot seem to get a function that works.

I have a sheet named "Master" with student reference numbers (A2), student names (B2/C2) and their four subjects (D2, E2, F2, G2).

I need a separate sheet for each subject, so potentially 30 additional sheets, and i would like for where a subject is mentioned in one of the four columns of the master, the students reference and names would then be copied and pasted into a row on that subject specific sheet. Leaving the subject sheets with a list of names and numbers of the students studying.

So you would have a student on the master sheet and then they would appear on four additional subject sheets.

Ideally it would also update subject sheets as new students are added. And additional information is being added to the subjects sheets so they cannot be cleared.

Is this possible with formula??

Thank you for your help in advance.

Hi,

Is this the sort of thing that you're after?

I have deliberately left off the "Art" worksheet, as I wanted to show you a feature of this workbook that I have incorporated that may or may not be of use. If you don't need this feature, let me know and I can grossly shorten the length of the formula.
1. Right-click on the "Biology" worksheet tab
2. Select "Move or copy..."
3. Tick "Create a copy" and select "(move to end)"
4. Rename the copied worksheet to "Art" and watch the formula in action
In short, this formula populates the list of students based on the worksheet name, so that worksheets can be copied to create new subjects without having to retype the formulas again

Here is the formula:

Formula:

=IFERROR(INDEX(Master!A$2:A$11,SMALL(IF(Master!$C$2:$C$11=REPLACE(CELL("filename",$B$1),1,FIND("]",CELL("filename",$B$1)),""),ROW(Master!$B$2:$B$11)-ROW(Master!$B$1),IF(Master!$D$2:$D$11=REPLACE(CELL("filename",$B$1),1,FIND("]",CELL("filename",$B$1)),""),ROW(Master!$B$2:$B$11)-ROW(Master!$B$1),IF(Master!$E$2:$E$11=REPLACE(CELL("filename",$B$1),1,FIND("]",CELL("filename",$B$1)),""),ROW(Master!$B$2:$B$11)-ROW(Master!$B$1),IF(Master!$F$2:$F$11=REPLACE(CELL("filename",$B$1),1,FIND("]",CELL("filename",$B$1)),""),ROW(Master!$B$2:$B$11)-ROW(Master!$B$1))))),ROWS(A$1:A1))),"")

Note that this is an array formula, and requires Ctrl + Shift + Enter, not just Enter to work.

I hope this helps

Hello!

This is amazing!!!
Thank you so much.
You have no idea, how many hours or fighting with formulas and visual basic to try and get this to work.

Thank you so much!!!
:-)

Can I ask, if I wanted to add a fifth subject column on the master sheet, so they have five options have would this be achieved?

Thank you.

You're welcome! I'm glad I could help.

Here is the 5th column added as you have requested

If this solves your problem, please don't forget to mark this thread as solved

Yes, that is perfect.
I think i'll have a study of the formula to see where i was going wrong.

Thank you again!
:-)

No problems

Please don't forget to mark this thread as solved

Originally Posted by ajryan88

No problems

Please don't forget to mark this thread as solved

It already is.