Why not have a row for every player, no need for the dropdown then, and the formulae just apply to that row's player.
I'm helping with a disc golf tournament this weekend and the tournament director asked that I come up with a solution to save time adding scores between rounds.
The Excel file has three tabs - 'Scores', 'Day 1 Rounds', 'Day 2 Rounds'.
Scores has the Players' names and scores for the four rounds (two on day 1, two on day 2).
The 'Scores' tab looks like:
Rd1 Rd2 Rd3 Rd4
Name
I have these formulas in the Rd1 - Rd4 cells
=SUMIF($R68,Day1_Round1,'Day 1 Rounds'!$V$2)
=SUMIF($R68,Day1_Round2,'Day 1 Rounds'!$V$7)
=SUMIF($R68,Day2_Round1,'Day 1 Rounds'!$V$2)
=SUMIF($R68,Day2_Round2,'Day 1 Rounds'!$V$7)
The 'Day 1 Rounds' and 'Day 2 Rounds' add the scores for each hole. There is a pull-down box to grab the player's name.
The idea is that the SUMIF formulas grab the scores from the 'Day 1 Rounds', 'Day 2 Rounds' tabs. The problem is (and I apologize for being so long-winded) is that when I change the player's name in the pull-down box, it changes the scores on the 'Scores' tab.
How do I ensure that the scores don't change on the 'Scores' tab once I've entered the scores on the 'Day 1 Rounds', 'Day 2 Rounds' tabs?
I know I need to adjust the SUMIF formulas.
Please let me know if this makes sense.
Thank you in advance for your help.
Best regards,
Fister
Why not have a row for every player, no need for the dropdown then, and the formulae just apply to that row's player.
I'd be skipping the formula aspect and doing this in a PivotTable. Example attached.
Ken Puls, FCPA, FCMA, MS MVP
Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training), with my book M is for Data Monkey, or our new Power Query Recipe cards!
Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter
If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.
Bookmarks