For the last little while, I've been building a master excel sheet for the hockey team I cover. I would like to take this to the next level. Please view the attachment to follow along while I try to describe what's in my head:
scoresheet.xlsx
I want to have a scoresheet for every game (56 in total, each on a different date) and have the information that I enter into it during a game, feed the players (21 in total, all different numbers) individual game logs. I've provided examples of both in the attachment.
In the case of the attachment above, I've entered 3 goals into the sheet, all scored by number 27. However I only really care about the ones scored by my players (marked as 'k' in the 'team' column). The 3rd 27, scored by team 'O' doesn't need to be tabulated anywhere else. So part one of the challenge is to tabulate the number of goals scored by a player, then use that information to fill their player game log (sheet '27') in the attachment.
HOWEVER...
I also want to track types of goals. So in this case, 27 scored a goal EV (even strength) and PP (powerplay). You can see cells available for all different types of goals, each with their own abbreviation (EV, PP, SH, EN, PS). So a goal from K #27 that is PP in type column (Y), then puts a 1 in I4 of sheet '27'. Then the next game, if 27 scores a SH goal, a 1 would be added to J5 on sheet '27'.
Does this make sense? I essentially want the scoresheet to automatically fill out the player logs for scoring. I'd like to extend this to penalties as well, but that's down the line and if I can get a handle for the required formula/technique, then I can hopefully apply it to other areas as well.
Bookmarks