# Formulas and Functions

Copper Contributor

# Formulas and Functions

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

# Re: Formulas and Functions

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

# Re: Formulas and Functions

=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.