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.

 

[Monty85]

Formulas are not my strong point. Could you please explain step-by-step what this formula does?

=IFERROR((LET(PLAYED_TO,$M$8:$M$35,SMALL(TAKE(FILTER(PLAYED_TO,ISNUMBER(PLAYED_TO)),$E$4),F3))),"")

I had no idea how to explain so I asked chat GPT to break it down - i think this isn't too bad a description.

1. FILTER(PLAYED_TO, ISNUMBER(PLAYED_TO)): This part filters the range $M$8:$M$35, named PLAYED_TO, to include only those values that are numbers. This is useful for removing any non-numeric entries from the dataset.
2. TAKE(..., $E$4): The TAKE function then takes the first E4 number of entries from the filtered list. The value in cell $E$4 determines how many of the smallest numbers to consider.
3. SMALL(..., F3): The SMALL function returns the F3-th smallest value from the array produced by the TAKE function. Essentially, if F3 is 1, it will return the smallest number; if F3 is 2, it returns the second smallest, and so on.
4. LET(PLAYED_TO, ..., ...): The LET function allows you to define a named variable (PLAYED_TO) for use within the formula, which helps in improving readability and performance.
5. IFERROR(..., ""): Finally, the IFERROR function checks if the entire expression results in an error (for example, if there are not enough numbers to fulfill the SMALL request). If there is an error, it returns an empty string (""); otherwise, it returns the result of the calculation.

Summary​

The formula effectively retrieves the F3-th smallest number from a filtered list of numeric values in the range $M$8:$M$35. If there are any errors (like not enough numbers), it returns an empty string instead.

 

[mumps]

Try this macro in the worksheet code module. It will be executed when you select a player in C1.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address(0, 0) <> "C1" Then Exit Sub
    Application.ScreenUpdating = False
    Dim player As Range, desWS As Worksheet, index As Range, last As Long
    Set desWS = Sheets("Expected Result")
    Set player = desWS.Range("A:A").Find(Target.Value, LookIn:=xlValues, lookat:=xlWhole)
    If Not player Is Nothing Then
        Cells(7, 1).Sort Key1:=Columns(13), Order1:=xlAscending, Orientation:=xlTopToBottom, Header:=xlYes
        For Each index In desWS.Range("D2:H2")
            last = Split(index, " ")(1)
            'desWS.Range("D" & player.Row).Resize(, last) = WorksheetFunction.Average(Range("M8").Resize(last))
            desWS.Cells(player.Row, Columns.Count).End(xlToLeft).Offset(, 1) = Format(WorksheetFunction.Average(Range("M8").Resize(last)), "#,###.##")
        Next index
    End If
    Application.ScreenUpdating = True
End Sub

 

[Monty85]

Try this macro in the worksheet code module. It will be executed when you select a player in C1.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address(0, 0) <> "C1" Then Exit Sub
    Application.ScreenUpdating = False
    Dim player As Range, desWS As Worksheet, index As Range, last As Long
    Set desWS = Sheets("Expected Result")
    Set player = desWS.Range("A:A").Find(Target.Value, LookIn:=xlValues, lookat:=xlWhole)
    If Not player Is Nothing Then
        Cells(7, 1).Sort Key1:=Columns(13), Order1:=xlAscending, Orientation:=xlTopToBottom, Header:=xlYes
        For Each index In desWS.Range("D2:H2")
            last = Split(index, " ")(1)
            'desWS.Range("D" & player.Row).Resize(, last) = WorksheetFunction.Average(Range("M8").Resize(last))
            desWS.Cells(player.Row, Columns.Count).End(xlToLeft).Offset(, 1) = Format(WorksheetFunction.Average(Range("M8").Resize(last)), "#,###.##")
        Next index
    End If
    Application.ScreenUpdating = True
End Sub

Thanks. Slight improvement, it now does the all the different handicap systems for each player name when you select it but it still requires me to select each player name individually to complete the table.

This is still better than what I had before but would still mean I'd need to run through the list and select every player name to update the table.

If there's a way that it can also do every player name as part of the single process I think that would leave with me a workable solution.

Thanks again.

 

[mumps]

Replace the current macro with the one below. Every player will be updated when you select any player in C1.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address(0, 0) <> "C1" Then Exit Sub
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    Dim player As Range, desWS As Worksheet, index As Range, last As Long
    Set desWS = Sheets("Expected Result")
    Set player = desWS.Range("A:A").Find(Target.Value, LookIn:=xlValues, lookat:=xlWhole)
    If Not player Is Nothing Then
        Cells(7, 1).Sort Key1:=Columns(13), Order1:=xlAscending, Orientation:=xlTopToBottom, Header:=xlYes
        With desWS
            lRow = .Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
            .Range("D3:H" & lRow).ClearContents
            For Each player In .Range("A3:A" & lRow)
                Target = player
                For Each index In .Range("D2:H2")
                    last = Split(index, " ")(1)
                    Range("F4:M4").ClearContents
                    Range("F4").Resize(, last).Value = Application.Transpose(Range("M8").Resize(last))
                    .Cells(player.Row, .Columns.Count).End(xlToLeft).Offset(, 1) = Format(Range("D4").Value, "#,###.##")
                Next index
            Next player
         End With
    End If
    Application.EnableEvents = True
    Application.ScreenUpdating = True
End Sub

 

[Monty85]

Replace the current macro with the one below. Every player will be updated when you select any player in C1.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address(0, 0) <> "C1" Then Exit Sub
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    Dim player As Range, desWS As Worksheet, index As Range, last As Long
    Set desWS = Sheets("Expected Result")
    Set player = desWS.Range("A:A").Find(Target.Value, LookIn:=xlValues, lookat:=xlWhole)
    If Not player Is Nothing Then
        Cells(7, 1).Sort Key1:=Columns(13), Order1:=xlAscending, Orientation:=xlTopToBottom, Header:=xlYes
        With desWS
            lRow = .Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
            .Range("D3:H" & lRow).ClearContents
            For Each player In .Range("A3:A" & lRow)
                Target = player
                For Each index In .Range("D2:H2")
                    last = Split(index, " ")(1)
                    Range("F4:M4").ClearContents
                    Range("F4").Resize(, last).Value = Application.Transpose(Range("M8").Resize(last))
                    .Cells(player.Row, .Columns.Count).End(xlToLeft).Offset(, 1) = Format(Range("D4").Value, "#,###.##")
                Next index
            Next player
         End With
    End If
    Application.EnableEvents = True
    Application.ScreenUpdating = True
End Sub

Thanks, I think this is workable.

When i first used it, it added data outside the table (underneath it). But once I removed the additional text that was there and re-ran it, its completed the table.

The Calculator tab becomes unusable when the code is in place (you can't select a player and look them up) but if i only use this code to pull the numbers accross and then remove it, it should work ok.

Thank you for spending the time troubleshooting this.

 

[Monty85]

Replace the current macro with the one below. Every player will be updated when you select any player in C1.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address(0, 0) <> "C1" Then Exit Sub
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    Dim player As Range, desWS As Worksheet, index As Range, last As Long
    Set desWS = Sheets("Expected Result")
    Set player = desWS.Range("A:A").Find(Target.Value, LookIn:=xlValues, lookat:=xlWhole)
    If Not player Is Nothing Then
        Cells(7, 1).Sort Key1:=Columns(13), Order1:=xlAscending, Orientation:=xlTopToBottom, Header:=xlYes
        With desWS
            lRow = .Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
            .Range("D3:H" & lRow).ClearContents
            For Each player In .Range("A3:A" & lRow)
                Target = player
                For Each index In .Range("D2:H2")
                    last = Split(index, " ")(1)
                    Range("F4:M4").ClearContents
                    Range("F4").Resize(, last).Value = Application.Transpose(Range("M8").Resize(last))
                    .Cells(player.Row, .Columns.Count).End(xlToLeft).Offset(, 1) = Format(Range("D4").Value, "#,###.##")
                Next index
            Next player
         End With
    End If
    Application.EnableEvents = True
    Application.ScreenUpdating = True
End Sub

So i'm still struggling a bit to modify this across the actual file, which has more data, and slightly different names.
Are you able to assist with what components I need to modify above to apply this to a different file?

I even tried to use your earlier example which did each player 1 by 1, but I can't seem to get that to work either.

The Debug in the macro seems to be unhappy with the bold line below.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address(0, 0) <> "C4" Then Exit Sub
    Application.ScreenUpdating = False
    Dim player As Range, handicap As Range, desWS As Worksheet
    Set desWS = Sheets("Handicaps")
    Set player = desWS.Range("A:A").Find(Range("C1").Value, LookIn:=xlValues, lookat:=xlWhole)
    If Not player Is Nothing Then
        Set handicap = desWS.Range("E2").Find(Target.Value, LookIn:=xlValues, lookat:=xlPart)
        desWS.Cells(player.Row, handicap.Column) = Round(Target.Offset(, 1).Value, 1)
    End If
    Application.ScreenUpdating = True
End Sub

edit: cant seem to bold the line - it is the 9th line im referring to.

 

[Peter_SSs]

edit: cant seem to bold the line - it is the 9th line im referring to.

If you want to apply your own formatting to vba, use the 'RICH' code tags instead of VBA. I have changed it for you on this occasion (& added red as Bold does not stand out that well by itself in the RICH tags)

 

[mumps]

It would be easier to help if you could upload a copy of your actual file, de-sensitized if necessary.

 

[Monty85]

That's more or less what I did, but i'm also trying to understand how this works so that I am able to adjust as things in the file inevitably move around over time.

 

[mumps]

So i'm still struggling a bit to modify this across the actual file, which has more data, and slightly different names.

If the macro is working on your sample file but not on your actual file, there must be some differences that are causing the macro not to work on your actual file. That is why it would be easier to find a solution if you could upload your actual file.