yogi_Compute Progressive Total And Individual Scores For Oscar Pool

                                          Google Spreadsheet   Post  #1005

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.    Feb 02, 2013

post by user: Robbo27 said:(https://productforums.google.com/forum/#!category-topic/docs/spreadsheets/0mNPWCrf_YE)
Auto-update formula across sheets 
(I'll try to state it simply but I'm not sure it makes sense. Context below if that is less confusing)

I have 9 sheets. I am trying to get A3 of every sheet to be a copy of the formula for A3 on sheet one, but all referencing their own sheets. I can get them to be exact copies of that 'Sheet 1'!A3 but I want them to instead copy the formula in 'Sheet 1'!A3 to then do the formula on their own individual sheets.

Here's the longer version since I feel like that didn't make much sense.

I made a sheets doc for my family's Oscar pool. The set up is that you wager points for each category. Best Picture has 9 nominees, so you spread out your 9 points however you want. If you think Argo is a lock, do all 9. Maybe it's Argo, maybe it's Lincoln... do 5 and 4. Then you get however many points wagered on the winner.

So I have an answer key as sheet 1. As the night goes I can just do a simple =SUM formula to add total points. So if Best Picture winner is A7 and Best Visual effects is A125, then I'll manually add the points to the winning boxes  on the score sheet and the total points formula will be =SUM(A7+A125) etc

What I'm trying to do is make each person's ballot reference that same formula. However if they thought Zero Dark Thirty would win so their A7 is empty, they would get no points.

So how can I get the formula to copy to each sheet live (Since winners will be added as the night goes on) but get it to look at each individual's ballot, and not just reference the main score sheet?

Does any of that make any sense?

---

Here is the doc, simplified. I filled out a few "correct" answers on the score sheet and purposely picked things different from the guesses on the "Rob" sheet so that the numbers shouldn't match. 

I want to be able to add to the function on the score sheet as the night goes on, and have that be reflected in the tally on the rest of the sheets. But only correct guesses should be counted. 

https://docs.google.com/spreadsheet/ccc?key=0AjeElCVO5sq6dC1NQmxwTkoySkZhUUprUGtsWnpCSkE&usp=sharing

Thanks for taking a look!

-------------------------------------------------------------------------------------------

in the following, I present a solution with Robbo27's layout modified a bit (to suit my illustration)