Forum Discussion
EXCEL FORMULA
CURTIS775 in the attached I updated both sheets 3 and 4 in a couple ways:
a) I added a new sheet 'Registration' where you can enter the names of all the players, their handicap and feel free to add payment and date and contact info if you want
b) I changed the formulas to ignore blanks so you don't get that '11' or other points when the cell is blank (i.e. looks like a zero)
c) I updated them to have 18 holes instead of just 9
d) I reconfigured the tables to be side by side so now 1 player had 1 row and the first ~20 columns are their golf scores and then the next are the points they earn on each corresponding hole and total points and such. This makes it easier to read (IMHO) and easier to expand between your weekly games and your 100+player tournaments
e) I did a lot of playing with the array formulas on sheet 3 so they auto fill based on the number of players and don't show any 'N/A' or other errors so now you add players on the registration sheet and bam they show up on that sheet ready to go. I also 'locked' that sheet leaving only the scores for each hole unlocked and the last column with the Total to date unlocked to prevent accidental mistakes on those formulas.
f) if you don't have array formulas and therefore sheet 3 doesn't work for you, then you will need to copy the formulas down on sheet 4 to meet the number of registrations you have.
When you copy sheet 3 or sheet 4 everything should work (i.e. just wipe out the previous scores) except that last column 'Total to date' is still 'manual' in that you will have to update it on each sheet. BTW, you have it adding the placement from week to week and not the scores/points from week to week, is that what you want or should it be adding the points?
I hope this is helpful for you.
How do I use the event finish column on event 3 to expand and encompass additional players and places?
- Thank you so much for your help. This is been a very good experience for me developing this sheet and learning from you.
CURTIS775 OK so the problem with that is it depends on how you arrange the sheets (and/or how you name the sheets). So in the attached I added some Excel 4 macros to the Name definitions which means I had to save it as .xlsb instead of .xlsx and when you open it, you will get prompted that it has macros and if you accept that. So if you are ok with this then the 'macros' I added are 3 name definitions that return the name of your current sheet (thisSheet), a refence to the same cell in the next sheet (nSheet) or previous sheet (pSheet). So based on how you added sheets to the beginning of the book so they go event 4, event 3, event 2, event 1 I used a formula using 'nSheet' to add then new amount to the previous total in the same cell in the next sheet. This works regardless of the sheet NAME but is dependent on the sheet ORDER (i.e. you can name the sheets 10/4/2020, 9/30/2020, ... as long as the ORDER is Newest to Oldest). you can easily reverse this using 'pSheet' instead.
An alternative that will avoid the macro complication but will rely on sheet ORDER AND the sheet NAME is to use a fixed sheet name prefix (like you did in that sample book with every sheet called "event " followed by a sequential number. In this case I added a name definition called "sheetNames" but that could be a fixed cell in the workbook (or just defined in the formula but I recommend against that). This version is used on sheet called "event 4".