Hi! I do a sport where you have to keep track of the amount of hours you spend practicing. I want to create a spreadsheet that helps keep track of this information for everyone participating in the sport.
I'd like to have an overview tab with each person's name and the total number of hours. I'd then like to have an individual sheet for each year. The individual sheets will have the numbers of hours they trained each month totaled and a total year-to-date column that sums that information.
For instance, say you have John Smith. He's been practicing for all of 2018 and 2019. The YTD column in the 2018 sheet shows 54. The YTD column in the 2019 sheet shows 47. I'd like his total number of hours on the overview page to show as 101.
However, I can't figure out how to calculate the total number of hours on the overview page. I've tried SUM('2019:2018'!N2). This works well unless the order of names changes (which it may if someone stops practicing). That's why I'd like to use VLookup. I've figured out VLookup for a single sheet. But I don't know how to make VLookup work on a range of sheets like the previous SUM formula does with '2019:2018'.
I'd also like to avoid just adding multiple vlookup formulas together since that list could get really long as more and more years pass. Which is why I was hoping to find a 3D vlookup formula that solves this issue.
I hope I explained all of that well enough. I've attached a workbook that contains sample information.
Any help would be greatly appreciated! Thank you!
Bookmarks