Formulas and Functions

Copper Contributor

I'll give you some backstory so you understand what I'm trying to accomplish. The school I work at accepts student enrollment based on a lottery process. Registrations are submitted, and then we draw numbers randomly from the list of applicants to fill our available spots on a per-grade basis. We're a middle school and have grades 4-9. As we receive the registrations, we enter the pertinent information in an Excel Workbook. The Workbook consists of 7 sheets.  Each sheet has a header row and columns from A:AD.  Columns A and B need to be left without a formula because we enter information manually in those two columns. The first sheet is a Summary. The Summary lists all applicants in grades 4-9. The rest of the sheets are labeled grade 4, grade 5, grade 6, grade 7, grade 8 and grade 9. I only want to enter the information on the first sheet, the Summary page.


I would like a formula in the subsequent pages that pulls the information from the Summary page to the corresponding grade page. i.e. Johnny Smith is in Grade 4. After I enter the information on the Summary page, I would like Johnny's information to show when I access the Grade 4 page. I know I can just go with one page and use the filter function, but my principal wants individual pages. 


I've had a little success with:  =IFERROR(INDEX(Summary!$C:$AD, MATCH(4, Summary!$J:$J, 0), COLUMN()-2), "")   The issue is I'm getting a page of duplicated information. I've allowed myself 50 rows per sheet (/grade). I've entered this formula in all cells from C2:AD51. What I ended up with was a sheet full of 'Johnny Smith in grade 4'. I tried adding a 'helper' column AE, with the following formula: =IF(COUNTIF($K$1:K1&$L$1:L1, $K1&$L1)>1, "Duplicated", "")    But I ended up with a page full of Johnny Smith. 


Are there any formulas that I can use that will automatically pull an entire row, based on the grade criteria without duplication? Or... do I have to tell my principal... 'your wish is not my command' and use the filter function?

2 Replies
looks some complicate.
If possible,share you data and your expected result which may help to understand your need.


=IFERROR(INDEX(Summary!C$2:C$1000, SMALL(IF( Summary!$J$2:$J$1000=4, ROW($J$2:$J$1000)-1),ROW($A1))),"")


An alternative could be this formula. The formula is in cell C2 of the "grade 4" sheet and filled across range C2:AD51. Enter the formula with ctrl+shift+enter if you don't work with Office 365 or Excel for the web or Excel 2021.


If i correctly understand column J in sheet "Summary" is where the grade is entered. The above formula selects all rows with grade = 4 in column J.






If you have access to the FILTER function you can apply the above formula in cell C2 of the "grade 4" sheet. This formula spills the results.