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 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)
All good, it works in the test file - i am testing the actual file now and will mark as resolved if all goes well.

Thanks for the help.
 
Upvote 0

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.
Cheers. Hope it works well but post back with details if any problems.
 
Upvote 0
Cheers. Hope it works well but post back with details if any problems.
So with a couple of minor tweaks to the code to suit the file, it works perfectly, but still returns an error.


1731053477542.png


Rich (BB 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("HC Calculator")
  For Each c In wsH.Range("A8", 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(, 4 + i).Value = Round(wsC.Range("D4").Value, 1)
      Next i
    End If
  Next c
  Application.ScreenUpdating = True
End Sub

The red line is what the debugger shows as the issue.

End of the day, it outputs everything correctly into the table despite this so I'm satisfied regardless.
 
Upvote 0
, it works perfectly, but still returns an error.
That is contradictory to me. If it returns an error, surely it isn't working perfectly. :confused:


The red line is what the debugger shows as the issue.

End of the day, it outputs everything correctly
Again, seems contradictory to me. If it shows an error, not sure how it can be outputting everything correctly. :confused:


Can you provide another sample file that demonstrates the above two issues?
 
Upvote 0

Forum statistics

Threads
1,223,877
Messages
6,175,137
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