Add to a cell already containing a number

[FivestarMac]

In a golf league of 8-14 4-man teams handicaps reduce gross scores to net scores. A player who earns a net score 1 or 2 below par on a hole earns 1 or 2 points for that team. The league plays once per week and runs for several months. I wish to compute each player's point contribution each week on the ActiveWeek sheet, then accumulate his points to date...by hole...in columns G-X on the Names sheet. For example, Abraham has previously scored points as shown in Names!G3:X3. This week he got a 4 on hole #1, net 3, and this gives his team a point.
Question: How can I capture this information and accumulate it in cell Names!G3 such that the new answer is 2? Do that for everybody (who plays) each week.

[dflak]

I am not familiar with golf, so some things that may be evident to duffers are foreign to me.

Let me see if I can explain this as I see it to make sure we have the same concept of the requirements.

Columns E:W are the scores the players attained on 18 holes (Column N being the break between front and back 9s - that much terminology I can understand )

Columns AA:AS are net for the holes (what is net?)

So you want to compare the score attained against the net. If Abraham scores 4 he gets 4-3 or 1 point. What does he get for a 5 or 2?

Also are the sheets for each week laid out exactly the same with players and their scores and is the net always the same on every sheet?

If so, then I suggest you name the sheets Week 1, Week 2 to Week n and lay them out in advance. You can hide the sheet that aren't the current week. Keep these sheets contiguous and you can use what they call a 3-D formula to sum the points as they are acquired each week.

I'll need the answers to the questions above and a workbook with a couple of week's worth of data, if the workbooks are set up the way I think they are and I set up the formula correctly, I'll only have to set up the formula in one cell and copy the rest down.

[FivestarMac]

Players are given a handicap based upon the best 10 of their last 20 rounds of golf. Abraham's handicap is 18 shown in cell Names!D4. He plays off the "H" tees which are shorter in yardage than the "1" tees, so there is an adjustment to handicap due to the easier tee and that adjustment is in cell Names!A1. After that adjustment Abraham's effective handicap is shown in ActiveWeekD4 as 16. The holes are handicapped for difficulty with #9 being most difficult at 1 and # 16 being easiest at 18. Abraham is entitled to a stroke off his gross score on any hole handicapped 16 or lower (all of them except #8 and #16.) When those strokes are taken away from his gross score the result is his net score for that hole. Example, he made 4 on hole #1...minus one stroke for handicap...net score 3 in cell AA4. Because that is lower than the par for that hole AA2 his team gets a "point." In my example Abraham contributes 11 points to his team that day BP4.

I had already thought of your approach to capture each day's play on a sheet and then accumulate across all sheets, and I will do that unless someone suggests a method which precludes saving all those sheets.

Thanks for your interest and help, however, and let's see what others may suggest.

[FivestarMac]

Dflak, let us say I will use a sheet for each week as you suggest. Each sheet will look like Names. What equations would you suggest for populating columns G-X in each of those sheets with current results from the just-played round? I think it might be Index Match but I am not able to do those.
Thanks.

[dflak]

My apologies for the late response. I was called unexpectedly out of town.

Assuming that each week has the same format with each player playing the same place on the same team, then you can use what is called a 3-D formula such as the following: =SUM('Week 1:Week 4'!AW4). This means to take the contents of cell AW4 in Sheet Week 1 and Sheet Week 4 and every sheet that is physically located between these two sheets and sum them together.

So make a sheet for each week and make sure they are contiguous. They don't have to be named Week 1, Week 2, etc. They can also be named 11-5, 11-12, 11-19 or Dec 2, Dec 9, or whatever as long as they are contiguous, you can use a 3-D formula.

There is a short example in the attachment. See what happens fif you move Week 3 out of the range or insert a new sheet in the middle of the range.

If the weekly sheets are not identical then you may need MATCH / INDEX - I am not too sure if I can figure out how to use a 3-D formula for that. I may have to use a series of formulas: one for each week and add them together.

[FivestarMac]

Yes, thanks, that is how I now have solved the problem, just as you described. Appreciate your input.