Golf Leaderboard

I am trying to create in Excel or Sheets a Leaderboard that automatically updates Stableford points for my Golf social club

Thanks for your reply. I probably did not explain as well as I could. What I am trying to do is create a spreadsheet that automatically updates once an entry is made. As the golfers come in with their scorecards they give me their Stableford score which I write on a sheet and then have to manually sort all the scores to work out the winner. I also need it to update the Leaderboard for each month and YTD. I have tried and can get the Spreadsheet to update the Monthly score after doing a manual sort for the Daily score and the ytd updates until I add the next mth into the equation it then sorts the scores without regard for the names. It sounds a bit complicated, hope you can understand what I am trying to achieve.

Thanks Ali for your reply. I probably did not explain as well as I could. What I am trying to do is create a spreadsheet that automatically updates once an entry is made. As the golfers come in with their scorecards they give me their Stableford score which I write on a sheet and then have to manually sort all the scores to work out the winner. I also need it to update the Leaderboard for each month and YTD. I have tried and can get the Spreadsheet to update the Monthly score after doing a manual sort for the Daily score and the ytd updates until I add the next mth into the equation it then sorts the scores without regard for the names. It sounds a bit complicated, hope you can understand what I am trying to achieve.

Sounds like you have something close to what you want.

Your best bet is to attach what you already have and to explain why it is not working and what you would like it to do.

I'll have a look at it and I'm sure there will be others too that will be willing to help.

Hi Croweater,
I can't believe that I saved over the spreadsheet, now have to try and re-create it in order to post it online.
Thanks everyone for your contributions, I feel like a real idiot.

Hi Everyone,
I have done a modified Leaderboard over several months to show what I am trying to achieve.
As you will see there are 3 months and a YTD.
My issue is that when I do a sort the numbers do not match the names.
I have not done the coding as I lost the original spreadsheet and will need to start over..

It is in Excel Hopefully it is attached as I can't see it.

I have Version 14.07 part of Microsoft Office professional 2010

I don't have MS 365 mine is about 12years old before 365.
I don't see why I need to do a profile I am just a 73 year old who needs some help with my spreadsheet.

Am I able to help or will that get me banned again?

Like I said I am an old man I don't know how to update my profile, nothing looks as per your pic you sent to do the update. All I want is someone to help with my problem I am not looking for a relationship.

Hey Crow eater, I will appreciate any help. I can't see how helping will get you banned.
Have attached my file it is in Excel around 2010 version. it says 14.07 don't know what that means.

Originally Posted by wiltwin50

Like I said I am an old man I don't know how to update my profile, nothing looks as per your pic you sent to do the update. All I want is someone to help with my problem I am not looking for a relationship.

start with "My Profile" up the top right....then look for the "About Me" tab....

Ok a couple of questions...

1. Exactly what range of cells (what sheets, what columns) are you saying that the sort is not working for?
2. You mentioned the word "coding" before. Are you trying/looking for a macro (vba) solution to your problem?
3. When I try sorting names using the bog standard excel sort, it works ok. Can you tell me how you are sorting it?

Hi Croweater,
I don't know what the correct term is. I said Coding but it was a formula I guess and as previously mentioned I saved over the top of it.
What I want to do is have all the columns sorted in Numeric order large to small then they should all add up in the YTD column.
My issue is when I sort the YTD column the numbers don't match the names i.e. the numbers sort large to small but the names do not sort with the appropriate score.
I started with all the names in alphabetical order and the put their score down for each month which adds up to the YTD "year to date" then when I sort with the highest number at the top the numbers sort in the correct order independently of the names, so Gary might end up with Joes score in the YTD but have Seri's score in say Jan or Feb columns. Does this make sense?

Ok When you select what column(s) you want to sort on, does it say to you that there are adjacent columns and ask if you want to expand your selection or continue with what you have selected?

If you have only selected the numbers and said continue with the current selection it won't bring the names along with the numbers.

I don't recall that but I will do the formula again on the test spreadsheet and see if it asks and what the effect is.
Thank you for your help and I will let you know the result.

OK I have done the sort it works with adding the other fields but as you can see (Attached) the names are not matching the scores as highlighted in yellow for Gary. His total should be 51 but after sorting his score is now 33. That is what I am trying to achieve, the Name should move with the score.

OK...I can see what is happening. You are sorting on a column which has relative rather than absolute references (F4 as opposed to $F$4) and when you sort on that column the formulas aren't giving you the expected result.

Now modern versions of Excel handle this better than older versions (i.e. they have modified functions like SUMIFS) which don't exists in the older versions.
So, as you have an older version and I'm not sure which functions your version has, I will give you a formula that works on my old version of excel so it should work on yours.

copy and paste this formula in C4;

=SUM(($F$4:$F$12)*(--($E$4:$E$12=$B4))) +SUM(($I$4:$I$12)*(--($H$4:$H$12=$B4))) + SUM(($L$4:$L$12)*(--($K$4:$K$12=$B4)))

Now (and this is important) when you paste this formula, DO NOT just hit enter to put the formula in. Hold down the Ctrl and Shift buttons, THEN press enter.
This will put curly brackets around the formula {.....} as a sign you have done this correctly.
DO NOT try to put these in yourself, if won't work.

Once you have done this, drag your formula down as normal.

Now your sort will/should work for you

Hi Croweater,
Have done as you said but it is not working as it should. I held the ctrl shift enter keys as you said. see attached

Because "Gary" in your YTD column has a space after it.

Unless you use functions like TRIM, Excel will not show "Gary " as being the same as "Gary"

I am a bit lost. Checked for space, could not see one but clicked on delete to make sure but Gary was still 30 points and not 51 as it should be?

I tried it see attached but still have Gary showing 30pts instead of 51pts.
I can't see where the 30pts is coming from because if you add straight across the total is 53.

Look at the name highlighted in Yellow for March with a total of 21 POINTS!!!

Does it say "Gary"? No it doesn't! It says "Gar". Put a "y" on the end of that name.

That now works for Gary but not for all the others. How do I apply it to the other names?

Hi Ali,
Thank you for all your help, however the sort function throws a lot of the totals out of sink with the names and some will not sort correctly. Have attached the latest that I have tried to sort largest to smallest.

Yeah, so glad you thanked Ali, because after all she has provided most of the help.

When you sort, Select Range B3:C12 by clicking on B3 and dragging to C12

Select sort (on my older version it is under the "Data" menu item then "sort".

Sort by points and make sure the button to say you have a header row is clicked.

I'm still trying to work out why you think "the sort function throws a lot of the totals out of sink with the names".

Did you see the EDIT (the bit I thought of after I posted) on post #37?

OK Got it thanks for your patience

I did it the way croweater said but I think as my version is quite old it works a bit differently. Some of the Mths worked as per his suggestion while January only worked after I included "JAN" & "PTS" above the column. But in the end it works so a big thank you to everyone that helped.
I did some additions to my profile.
Cheers

Hi Ali,
I think I spoke too soon. I applied the formula to my main sheet with the same filed names and positions but the numbers and points totals did not match. The only one that matched Was Gary E. Would the fact that the real spreadsheet has two names per person instead of just one make any difference?

I did not send the original as the forum says not to send sensitive material. But it is just a golf club list so I guess not much that could be taken from the list. I put the formula into the first field but if you drag it down you will see the effect. Also I want to be able to then apply it to the next tab Apr-Jun
See Attached

...and while we're at it, if you don't trust yourself not to put extra spaces at the beginning and end you may as well trim all the ranges...

=SUM(($F$4:$F$100)*(--(TRIM($E$4:$E$100)=TRIM($B4)))) +SUM(($I$4:$I$100)*(--(TRIM($H$4:$H$100)=TRIM($B4)))) + SUM(($L$4:$L$100)*(--(TRIM($K$4:$K$100)=TRIM($B4))))

Just copy it to the next sheet.

If you want to add the previous tabs total (for a true YTD figure) you will need to add this to the end of that formula above;

+ IFERROR(VLOOKUP(TRIM($B4),'Jan-Mar'!$B$4:$C$100,2,0),0)

Don't forget to enter the whole thing with a CSE.

The data should be sorted in each column high numbers at the top. When you drag the equation down it changes all the numbers. The only one that is correct is the top one in the YTD column.
If you drag it down you we see what I mean.

Yes...and I told you (see post #51) the reason....because the real data was larger than the data range you originally gave.

I also showed you how to account for that larger range in post numbers 51 and 53, by giving you modified formulas for the larger ranges. Have you applied either of them?

As I explained, the second one takes out leading or trailing spaces from all names in the worksheet, (but it won't account for misspelled names).

Here...I've done it for you.

Hi Croweater,
I thought I did what you told me to do but obviously no correctly. I copied and pasted the formula, then held the Shift Control keys and pressed return, checked for spaces and misspelt words. But obviously I was not holding my tongue correctly, that is the only thing I can think of.
Hey Croweater a Big Thank You to you and Ali for putting up with an Excel illiterate.

Hey Crow Eater,
One last question.
My Spreadsheet ahs 4 tabs i.e. Jan-Mar / Apr-Jun / Jul-Sep / Oct-Dec
Now to apply the formula to Jul-Sep & Oct-Dec. What do I change? as I tried changing ,'Jan-Mar' to Jul-Sep but that did not work
,'Jan-Mar'!$B$4:$C$100,2,0),0)

All I changed was the Tab names within the brackets and left everything else as it was.
But Once again a big thank you

Originally Posted by wiltwin50

Hi Croweater,

Hey Croweater a Big Thank You to you and Ali for putting up with an Excel illiterate.

No problems. Us old blokes have to stick together. Brought back memories of trying to teach my kids the Sine and Cosine rules!