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

[Monty85]

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.

 

[Peter_SSs]

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.

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

Here are my results:

Monty85 Test_File.xlsm
	A	B	C	D	E	F	G	H
2	Name		Old Index	Best 3 from 8 Index	Best 4 from 8 Index	Best 4 from 10 Index	Best 6 from 16 Index	Best 8 from 20 Index
3	Player_A		11.9	6.4	6.6	6.6	7.1	6.6
4	Player_B		7.3	6.1	7.2	6.8	5.4	5.8
5	Player_C		16.9	14.9	15.6	15.6	16.6	17.6
6	Player_D		17.9	12.7	13.3	13.3	13.4	14.2
7	Player_E		3.3	3.9	4.3	4.3	4.7	4.2
8	Player_F		18.2	19.4	20.1	20	16.9	15.2
9	Player_G		14.6	13.1	13.8	13.8	13	13.8
10	Player_H		15.1	14.1	14.6	13.4	14.3	15.1
11	Player_I		9.0	10	12.1	12.1	12.1	12.1
12	Player_J		12.6	14.7	15.5	14.2	15.1	16.1
13	Player_K		18.1	16.5	17.3	16.9	17.5	17.6
14	Player_L		23.3	19.6	20.8	19.6	21.7	23.2
15	Player_M		7.4	7.8	8.6	8.6	7.2	7.6
16	Player_N		15.3	16	16.3	15.7	14.5	13.8
Handicaps