Hi
We are old golfers trying to figure out another way to loose money among ourselves. We want to run a year long competition that records our lowest score on each hole during the year. In the attachment B4 is the weekly entry. B5 is the net score for that week and we want it compared to B8 which is the low recorded to date . If B5 is lower the it replaces the entry in B8 and if it is higher then B8 remains as is.
Any help will greatly assist the saving of our already thinning hair
Here you go fella's
I was thinking along the same lines as BlindAlley as far as the helper cell goes, but, I had another approach for the rest of it. Using BlindAlley's example, place the following formula in C9..
Please Login or Register to view this content.
1N73LL1G3NC3 15 7H3 4B1L17Y 70 4D4P7 70 CH4NG3 - 573PH3N H4WK1NG
You don't have to add Rep if I have helped you out (but it would be nice), but please mark the thread as SOLVED if your issue is resolved.
Tom
Totally embarassed. My explanation should read If B6 (net score ) is compared to B8 and if lower B6 replaces B8 and if equal to or higher do nothing Sorry for the confusion
Look at Sheet2 as a way (assumes you play weekly). You could have a sheet per player and "team" sheet representing the best score overall.
I've changed the formulas for you to reflect that.
Blind Alley
I have looked at your solution and I believe there may be a misunderstanding. If I read your solution correctly the lowest score of all the holes combined would be reported. I am attempting to get the lowest score for each hole independently. For example if 30 games were played I want the lowest of all the 30 hole 1 scores for hole 1 reported. The same for holes 2 thru 9. Hope this makes sense
JohnTopley
That is the solution however since there are 50 players for 25 weeks at 18 holes I was hoping to keep the size of the matrix small so that I do not have to scroll to find each player. Any suggestions
.... which is what I offered!!!
I'll leave this in your capable hands JohnOriginally Posted by JohnTopley
See attached.
"Team" sheet finds the minimum score per whole achieved over all players, represented by Sheet2 and Sheet3 (2 players). The Week figures exclude the handicap but could easily be included - as per formula for "Best Score".
The formula works by including sheets in a range. a Good trick is to add a (empty) sheet called "First" before your player sheets and one called "Last" after the last player sheet.
The formulae then become MIN(First:Last! C17 etc) which allows you to add/delete players without needing to change the formulae in "Team"
Per whole!! Per HOLE!
Last edited by JohnTopley; 07-11-2015 at 04:39 PM.
JohnTopley
Your solution will work so I am marking the problem solved. In order to assist in scrolling through 50 separate pages do you know of a method that will take me from the summary page (page 1) to the appropriate detailed work page in the workbook. Any help is greatly appreciated
Again see attached.
On "Team" sheet Column N is list of players (tab names): in Column O are (hyper)links to those sheets. Click on O celland you will go the sheet.
I'll sign off now but I'll be back sometime tomorrow!
JohnTopley
Thank you very much for all your help
FYI:
If you are interested I have written some simple VBA routines which will (A) Create a tab for each player based on list of players (B) allow you to add a new (tab) or (C) remove a player (tab). It creates the sheet(s) based on a "Master" sheet which is where you could hold the hyperlinks..
If I don't get a reply, I'll assume a NO.
JohnTopley
I have reviewed your suggestions and discovered a minor issue. The HCP of a player may change during the year. I can control this through a lookup table which is working
for another competition. Your solution will work if:
C2 is score to be entered each week for hole 1 D2 is hole 2, E2 hole 3, etc.
C3 is the hcp for hole 1 via the lookup table
C4 is the net score
C7 is the start of the summary table in which today's net score will be copied from C4 for hole 1
C8 is tomorrows entry copied from C4 for hole 1 etc. The min statement you suggested will then be correct
I have been playing with if statements (using an cell designated as week 1,2,3,4,etc.) and match statements but I keep losing any data already in the summary table.
Any suggestions would be greatly appreciated.
You will need to use VBA to do this as formulae automatically update when any data referenced in the formulae changes.
Tank you very much for your help
How do you want to proceed from here?
I have spent the afternoon reading up on VBA and have concluded that I lack the fundamental knowledge to write the program. I have been retired for 20+ years so you know this type of programming has passed me by. I have currently running some macros that people were kind enough to assist me with specific problems. If you are interested I would certainly appreciate your help. If you want the challenge let me know and I will rough out a proposed workbook page for a single player and once that is solved I am confident that I can prepare the total wkb. I found in the past that once the macro is written I can follow it and make simple modifications as required. Again many thanks for your help to date and please let me know if you want me to prepare a draft players page.
Thank you for your reply: I am happy to continue working on this until (all being well) we get a satisfactory solution.
I have started on a VBA solution based on the data being in rows 1 to 4 as per your earlier posting. Let me finish this and I'll post on the thread.
The VBA is very basic (as my own is now somewhat rusty) so I don't think it would be too difficult to modify.
Here is a VBA based solution.
The main driver is the "Dashboard" where you can create the initial tabs for players, add new players and remove players.
On the "Master" sheet (and hence each) tab there is button which activates the copy from row 4 to the appropriate week. It also records a timestamp when the update was executed.
There is currently no check to stop overwriting a week's data if, for example, the week number against the score (row 4) is not updated.
So you might want an added check against the timestamp to see a week's score has been previously updated.
JohnTopley
Thanks so much for your effort. I have spent this afternoon with the VBA tutorial and then playing with your solution. This model is very close to what I think will work. When I was playing today the following conditions were noted:
On the "Dashboard" I tried to introduce Bill, Brian, Jane and Fred as initial players. Fred would not enter as a new tab. I recieved Brian as an error (which was correct) as he was already entered.
I was unable to enter any new players or delete any existing players.
I entered scores for Brian and all went well after I realized that I had to change the week # in A4. The week number is not critical, especially since you built in the entered "date". Is it possible to omit the week # as the active cell place the weeks scores too after I enter the scores the program enters this data in the first blank row. (some members will miss various weeks) but that is not important.
When I entered several weeks of scores for Brian the Min function was still blank.
Hope these comments help and again thank you so much.
My fault as I omitted to say that to ADD new players put "A" or "a" in the Action Column and similarly put "D" or "d" to delete.
For minimum scores I'll change the formula to simply ' =MIN(C7;C18) or whatever the range.
On the last, point you simply want the "last" score entered into the next available row: O.K will do.
I have updated the VBA so you now only have one "Update" button on the "Dashboard". You need only enter "D" (or "d") in the "Action" column to remove a player and the corresponding sheet.
If "Action" is left blank an addition is assumed with a message for new players only. Hyperlinks are also added via the VBA and apply the workbook name so changing the workbook name should not require any VBA changes.
The "Add Score" function now puts the score in the next available row (from 7 onwards).
And I changed the "Best Score" to a simple =MIN( ...) test.
The changes you made are perfect. I have spent some time playing and all seems to be working. I have attached another WB in which I have added player Al. I then tried to input the Lookup table to see if I could get the HCP row working. Although it is not working at present I would like to clarify two questions
If I want to expand the number of weeks we play (to 20) can I just insert rows or does it require a modification to VBA controlling the Master Sheet?
On the attachment in Al's sheet I pasted the HCP table for our course. On row 24 I tried to duplicate your row 3 C:T as I did not want to interfere with your format. A24 would be the players current HCP and that then updates the HCP strokes in Row 24 C:T. The HCP can change quite often. Do I put this lookup in each individuals sheet or can it be included in the "Master" once I get it working? If included in the "Master " does it require VBA code or is the standard lookup formula added later acceptable?
All changes incorporated including VLOOKUP in Master (defaulting to Player 50) and added rows: no change to VBA required. You can add further rows as this does not affect VBA nor MIN calculation which uses relative references so will adjust automatically.
With VLOOKUP you can use names/numbers as required so just change DASHBOARD & HANDICAPS as needed.
Renamed worksheet to HANDICAPS.
John
Absolutely perfect. Somehow thanks does not adequately portray my appreciation for all your effort. When we get back to England I owe you lots of beer! Now us old guys can loose our life savings to one another with this silly golf game .
Cheers and again thanks
Al
It has been a pleasure working with you so thank you for all support
From my side it was good to able to do a complete "project" and see it to a satisfactory conclusion.
All the best to your fellow golfers.
From one "Old Guy" (really!) to another.
And I look forward to that beer!
P.S. You can always PM if needed.
John
JohnTopley
Sorry to trouble you again BUT.....
Today I was going to enter actual data for some players. I was able to add several players but when I went to delete the "test" players Bill deleted OK but when I tried to delete Fred I got :
Run_time error'1004'
method 'Range' of object'_Global'failed
When I went to the "debug" button I got :
update_players_sheets
Set P_ring = Range ("Player") highlighted in yellow.
Any suggestions? Thanks again for your help.
"Player" should be "Players": look at Formulas=>Name Manager and check the name.
I looked at the last version I sent you and it has RANGE("Players") so I don't know why this had happened!
That was my fault I made a typo. The range is "players".
Spent some time playing and teh following is noted
I can delete Bill but not Fred
I can delete from the bottom up till I get to Fred then system hangs
I added 20 names and then deleted them all OK
Sometimes I have been able to delete all names but then the system hangs and I cannot add anyone
I have saved the program under a different name but the problem persists
I created a problem by trying to be too clever: when you delete all the players the NAMED Range reference get corrupted as it is "dynamic".
Change "Players" to Refer to: =Dashboard!$A$2:$A$60 (or whatever upper limit you want).
My apologies.
You do not have to apologize considering the amount of time you have donated . I revised the macro as you suggested and all appears to be working well. I have added and deleted several players and the system is working as intended.
Unfortunately I have messed things up. I inserted 4 lines on the "Master" so that I could insert a name that I copied from the dasher board. I did this as a double check that I select the correct "Tab" before inputting the scores . Now when I input scores all goes well (including HCP updates) until I go to "Update Scores" button. The date line is updated but the scores are blank. You will note that I have also added a summary sheet but I am sure that has no impact as it only references the "Best Sore" line.
I have inserted my updated doc for your info if necessary. Any help would be much appreciated.
I have put a test in the code to find the line with "SCORE" in column B (currently 6): it was hard-coded so that why adding a few lines caused the problem. So hopefully that is another problem resolved!
I hate to do this to you but now the updates are working but they are updating from the "score" line but should be updating from the "Net Score " line. I added two columns in order to get a total for the score line but do not believe this would cause the problem.
Check from "Net score"
I have just finished entering as, a test, the scores of ten players for the year to date and the system worked perfectly. Again many thanks.
Glad all is now well. Thank you for your patience - now go and enjoy your golf.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks