Can excel return results that are calculated from separate items in a drop down list

Monty85

Board Regular
Joined
May 6, 2019
Messages
62
Office Version
  1. 365
Platform
  1. Windows
I have a spreadsheet I use for a social golf event to track scores and also to calculate each person's handicap.

The calculation for the handicap is done by selecting a players name from a drop down list which a cell then references as part of a lookup to return that players scores. A seperate cell than uses a formula which references these scores to calculate a handicap.

So in order to view a handicap, you first need to select the persons name from the drop down list and then make a note of each one manually and individually.

What i'm trying to work out is if its possible to have excel return the results into a table on another tab for every players handicap that is calculated from this page?

I'm not sure how best to provide an example file as the file in question has quite a lot of moving parts to it so thought i'd try to ask in text form first.

As always, thanks in advance.
 
I am a bit surprised that you want to fill all the yellow cells on the 'Handicaps' sheet. I would have thought that "Best 3 from 8" would not apply to somebody with 23 rounds.
Anyway, you can try this with the sample file. It does take a short while to complete so don't be too surprised if nothing seems to be happening for a few seconds.

BTW, one of the first things I did with the sample file was to remove the leading space from the ' Calculator' tab name.

VBA Code:
Sub Do_Handicaps()
  Dim wsH As Worksheet, wsC As Worksheet
  Dim c As Range
  Dim AllHCapSys As Variant
  Dim i As Long
  
  AllHCapSys = Split("Best 3 from 8,Best 4 from 8,Best 4 from 10,Best 6 from 16,Best 8 from 20", ",")
  Application.ScreenUpdating = False
  Set wsH = Sheets("Handicaps")
  Set wsC = Sheets("Calculator")
  For Each c In wsH.Range("A3", wsH.Range("A" & Rows.Count).End(xlUp))
    If Not IsEmpty(c.Value) Then
      For i = 0 To UBound(AllHCapSys)
        wsC.Range("C1").Value = c.Value
        wsC.Range("C4").Value = AllHCapSys(i)
        DoEvents
        c.Offset(, 3 + i).Value = Round(wsC.Range("D4").Value, 1)
      Next i
    End If
  Next c
  Application.ScreenUpdating = True
End Sub

Here are my results:

Monty85 Test_File.xlsm
ABCDEFGH
2NameOld IndexBest 3 from 8 IndexBest 4 from 8 IndexBest 4 from 10 IndexBest 6 from 16 IndexBest 8 from 20 Index
3Player_A11.96.46.66.67.16.6
4Player_B7.36.17.26.85.45.8
5Player_C16.914.915.615.616.617.6
6Player_D17.912.713.313.313.414.2
7Player_E3.33.94.34.34.74.2
8Player_F18.219.420.12016.915.2
9Player_G14.613.113.813.81313.8
10Player_H15.114.114.613.414.315.1
11Player_I9.01012.112.112.112.1
12Player_J12.614.715.514.215.116.1
13Player_K18.116.517.316.917.517.6
14Player_L23.319.620.819.621.723.2
15Player_M7.47.88.68.67.27.6
16Player_N15.31616.315.714.513.8
Handicaps
 
Last edited:
Upvote 0

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
I am a bit surprised that you want to fill all the yellow cells on the 'Handicaps' sheet. I would have thought that "Best 3 from 8" would not apply to somebody with 23 rounds.
Anyway, you can try this with the sample file. It does take a short while to complete so don't be too surprised if nothing seems to be happening for a few seconds.

Without going into too much detail, the way it works is it looks at the best 3 rounds from their most recent 8 rounds, which won't be the same dates (or rows in this context) for each person.

The multiple options/systems is because we are still trialling which system to use for our club.

Thanks for the input - I will test and let you know how it goes.
 
Upvote 0
I am a bit surprised that you want to fill all the yellow cells on the 'Handicaps' sheet. I would have thought that "Best 3 from 8" would not apply to somebody with 23 rounds.
Anyway, you can try this with the sample file. It does take a short while to complete so don't be too surprised if nothing seems to be happening for a few seconds.

BTW, one of the first things I did with the sample file was to remove the leading space from the ' Calculator' tab name.

VBA Code:
Sub Do_Handicaps()
  Dim wsH As Worksheet, wsC As Worksheet
  Dim c As Range
  Dim AllHCapSys As Variant
  Dim i As Long
 
  AllHCapSys = Split("Best 3 from 8,Best 4 from 8,Best 4 from 10,Best 6 from 16,Best 8 from 20", ",")
  Application.ScreenUpdating = False
  Set wsH = Sheets("Handicaps")
  Set wsC = Sheets("Calculator")
  For Each c In wsH.Range("A3", wsH.Range("A" & Rows.Count).End(xlUp))
    If Not IsEmpty(c.Value) Then
      For i = 0 To UBound(AllHCapSys)
        wsC.Range("C1").Value = c.Value
        wsC.Range("C4").Value = AllHCapSys(i)
        DoEvents
        c.Offset(, 3 + i).Value = Round(wsC.Range("D4").Value, 1)
      Next i
    End If
  Next c
  Applica
Is the order of operations the same for this code?

code is pasted in the "Calculator" tab and then triggered by selecting an option from one of the drop down boxes?

I've tried this but it doesn't seem to do anything for me? How long would you expect before I should see results? Waited a couple of minutes but still haven't seen any sign of life.

Edit: Is there code missing from the end of the example potentially? Looks like a word is cut off?
 
Upvote 0
Edit: Is there code missing from the end of the example potentially? Looks like a word is cut off?
Yes there was. I had edited and re-pasted my code just before posting and didn't notice that I had missed a bit. I have now corrected it in my previous post (& added one more line of code).


Is the order of operations the same for this code?

code is pasted in the "Calculator" tab and then triggered by selecting an option from one of the drop down boxes?
No. To test my code in a copy of your workbook ..
  • Remove any Worksheet_Change code that you have in the Calculator and/or Handicaps worksheets
  • Use the vba editor menus to Insert -> Module and paste my code into that new standard module (not a worksheet module) & save as an *.xlsm workbook
  • Back at your worksheet (any worksheet) press Alt+F8, choose 'DoHandicaps' and click 'Run' (The macro could be triggered by clicking a button or shape in the workbook as it looks like you may already have other things triggered this way)
 
Upvote 0
Yes there was. I had edited and re-pasted my code just before posting and didn't notice that I had missed a bit. I have now corrected it in my previous post (& added one more line of code).



No. To test my code in a copy of your workbook ..
  • Remove any Worksheet_Change code that you have in the Calculator and/or Handicaps worksheets
  • Use the vba editor menus to Insert -> Module and paste my code into that new standard module (not a worksheet module) & save as an *.xlsm workbook
  • Back at your worksheet (any worksheet) press Alt+F8, choose 'DoHandicaps' and click 'Run' (The macro could be triggered by clicking a button or shape in the workbook as it looks like you may already have other things triggered this way)
Don't seem to be able to get it to work weirdly.... I get the following error.

Nevermind, it was the sheet name that wasn't matching.... works now.

Thank you.
 

Attachments

  • 1731049805920.png
    1731049805920.png
    5.6 KB · Views: 3
Upvote 0
it was the change of the sheet name from "Expected Result" to "Handicaps"
At the time that I downloaded the test file from DropBox (way back when you first provided it I think) there was a tab called 'Handicaps' and no tab called 'Expected Results'. I assume the file must have been replaced/updated at some point after I downloaded. Anyway, glad you sorted that issue. (y)

No comment on how the code went after making that change though?
 
Upvote 0
I suspect that you didn't read my earlier post carefully enough ..
I read it very carefully & there was no mention of "works now" - but apparently you edited it after I had read it & edits are not notified so there was no reason for me to go back to the post again. :biggrin:

Anyway, glad that it is working for you now. (y)
 
Upvote 0

Forum statistics

Threads
1,223,877
Messages
6,175,138
Members
452,614
Latest member
MRSWIN2709

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top