If formula to gather data from different sheets in a worksheet

[lizsantiago07]

Hi!
I was wondering if someone could help me. I am a teacher and I need to gather info on students engaged in distant learning. I need to use a formula (I was trying with the IF with no luck) to bring the names of students with a 0, 1, 2, 3 from 3 different sheet to another sheet. So for example, I need all names from sheet lsantiago, Dfoster, and Apersaud that have a 0 to go to the sheet 0 students, if they have a 1 go to the sheet 1 students and so on. Can someone help me to figure this out. I attached a demo sheet.

Thanks!

[Eastw00d]

You can do this with a.o. the AGGREGATE function:

Please Login or Register  to view this content.

Per column in the "# students worksheets" you have to change the maximum row, if the worksheet contains more rows (in above case worksheet lsantiago contains 22 rows)
so if for example the worksheet lsantiago contains 25 rows, you have to change that in each of the 4 "# student"-sheets in the formula in the column lsantiago.


I have worked this out for your workbook, please see attachment
Cheers
Erwin

[Bo_Ry]

Key Student no. in A1, students sheet name in I1:I3,


E3 helper column
=IFERROR("'"&INDEX($I$1:$I$3,SMALL(IF(COUNTIFS(INDIRECT("'"&$I$1:$I$3&"'!B1:B90"),$A$1)>=COLUMN($A$1:$L$1),ROW($I$1:$I$3)),ROWS(E$3:E3)))&"'!","")

A3:B3
=IF($E3="","",INDIRECT($E3&"R"&SMALL(IF(INDIRECT($E3&"B1:B90")&""=$A$1&"",ROW($A$1:$A$90)),COUNTIF($E$3:$E3,$E3))&"C",))