PDA

View Full Version : Mulitple Tabs/Fomulas Help

Fister
2012-04-19, 09:29 PM
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

Bob Phillips
2012-04-20, 12:17 AM
Why not have a row for every player, no need for the dropdown then, and the formulae just apply to that row's player.

Fister
2012-04-20, 12:56 AM
Why not have a row for every player, no need for the dropdown then, and the formulae just apply to that row's player.

The file is pretty big, I was trying to avoid making it any "heavier".

Ken Puls
2012-04-20, 04:53 AM
I'd be skipping the formula aspect and doing this in a PivotTable. Example attached.