0

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. Data source example Report Example

Joseph T
  • 13
  • 4
  • 1
    Sounds like you can do exactly what you want with PivotTables, have you look into it? – shinjijai Dec 16 '19 at 19:26
  • If you get satisfactory answer(s) to this question as is, then ignore these comments (but keep them in mind for the future).  I believe that this question is unclear.  I have only the foggiest of ideas what your spreadsheet looks like.  For example, why are there 5000 rows when there are only ‘‘700+’’ athletes and ’’each athlete has a row’’?  Will an athlete who participates in 42 sports have 14 rows?  It took me ten minutes to realize that you had posted two separate images,  … (Cont’d) – Scott - Слава Україні Dec 16 '19 at 21:39
  • (Cont’d) …  and I still don’t understand how they relate.  Are they separate ***sheets*** in the same ***workbook*** (i.e., Excel file)?  Things like that should be explained *in words* in the question.  Sample sheets are more useful when they show data.  I understand that your real data are private, so use Andrea, Barbara, Charlie, Denise, Edith, Francine, etc.  And I seriously have no clue what you are trying to illustrate with the second image. … (Cont’d) – Scott - Слава Україні Dec 16 '19 at 21:40
  • (Cont’d) …  (And, when you crop off the row numbers and the column letters, that makes it harder for people to give clear, useful answers.) And your question would be a lot clearer if you showed an illustration of the result you want, that corresponds to the input data you have shown (i.e., uses the same A, B, C example names). Furthermore, we prefer that you [not post images of text.](//unix.meta.stackexchange.com/q/4086/23408) Post a textual representation of your data, as was done [here](https://superuser.com/q/1289084/150988), [here](https://superuser.com/q/889201/150988), … (Cont’d) – Scott - Слава Україні Dec 16 '19 at 21:40
  • (Cont’d) …  [here](https://superuser.com/q/443967/150988), [here](https://superuser.com/q/684851/150988), [here](https://superuser.com/q/815333/150988) and [here](//superuser.com/q/892744/150988); use the [Format Text as Table](https://senseful.github.io/text-table) or the [Plain Text Tables generator](http://www.tablesgenerator.com/text_tables) site if you want. And, have you tried anything? Where are you stuck? … … … … … … … … … … … … … … … … … … … … … … … … … … … Please do not respond in comments; [edit] your question to make it clearer and more complete. – Scott - Слава Україні Dec 16 '19 at 21:40
  • @shinjijai I have tried pivot tables but it is just not visually appealing. Maybe I need to find some videos on formatting pivot tables, but as of now one row with 48 columns for each athlete is not very palatable when printed. – Joseph T Dec 17 '19 at 14:37

2 Answers2

0

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.

Bansheete
  • 11
  • 3
0

I have figured out a way to summarize the data using pivot tables. Thank you for y'alls time and patience. Happy holidays!

Joseph T
  • 13
  • 4