Dynamic Tab Reference and dynamic cell reference in a formula

[mattstoc]

I want a spreadsheet to summarize test scores. Each test has multiple categories and each student can take a test multiple times. Think high school students with SAT and ACT. I have a sheet for each test (i.e. a tab for "SAT" and a tab for "ACT"). I also have a "Summary" tab with a list of students. I want a single formula that can be copied across cells that gets the max score for each section. Following is a link to the spreadsheet - TestScoresSummary

I posted a screenshot of the summary table. I accomplished with indirect and offsets, but it is resource intensive and frankly hard to follow the logic of the formulas. I also am explicitly try to avoid array formulas (ctrl+shift+enter) and VBA. This is going to be used by a friend who won't be consistent with Array Formulas and her machine will not allow macros.

Thanks in advance

P.S. - the link above opened in google sheets (at least for me) and that does not support the formulas I used. However, once opened in GoogleSheets, you can download as an excel file.

 

[stigcorneer]

Have you tried the =MAX() function?

 

[kvsrinivasamurthy]

Select B3:C3. use fill handle in right bottom corner of C3 and drag across till end. Now use fill handle in last cell and drag down.