Good afternoon. I am a high school Strength and Conditioning coach and want to provide my athletes with progress reports based on data I keep in a master data sheet. This spread sheet has over 5000 rows and 79 columns (700+ athletes). Each athlete has 7 rows of data total (one each for 6 different assessment times and a TOTAL CHANGE row). Each row has assessment results with absolute and relative change. I would like to be able to select an assessment period (example: Summer 2019), a sport, and then pick from the names of all of the athletes in that sport. Upon clicking their name I would like other cells in the Report Template to auto populate. First picture is an example of the data source. Second picture is an example of the report template the athlete would receive/where I want to place the drop downs. The report will be on a separate sheet in the same workbook. 
- 13
2 Answers
Totally doable, you would need some array formulas if you intend to depend only in a one sheet report. So, that would be many vlookups with an IF determining the range.
Example using your data, for Body Weight in what would be C4 of the second file:
=VLOOKUP(C2,IF(IF(1stfile!A2:A7=A2,1stfile!E2:E7)=B2,1stfile!C2:M7),10,FALSE)
It is an array formula so, after inputting it, you need to press CTRL+SHIFT+ENTER.
This should theoretically work but would need to be tested in a file. If you want you could upload those samples changing the names for fake ones so we can test the model.
But it is complex and you will need a formula for each field, and Array formulas. When there are too many it usually affects performanceradically, but could work.
- 11
I have figured out a way to summarize the data using pivot tables. Thank you for y'alls time and patience. Happy holidays!
- 13