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.

 

[mumps]

You could use the XL2BB add-in (icon in the menu) to attach screenshots (not pictures) of your sheets. Alternately, you could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Explain in detail what you want to do referring to specific cells, rows, columns and sheets using a few examples from your data (de-sensitized if necessary).

 

[Monty85]

You could use the XL2BB add-in (icon in the menu) to attach screenshots (not pictures) of your sheets. Alternately, you could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Explain in detail what you want to do referring to specific cells, rows, columns and sheets using a few examples from your data (de-sensitized if necessary).

Thanks.
I've stripped out as much as I could from my file to try and keep this as cut down as possible and have saved a test file here:

Dropbox

www.dropbox.com

There's some text in the tab labelled 'handicap' which describes what i'm trying to achieve.

Basically, I have a tab called 'Calculator' which after selecting some options from 2 drop down lists, will calculate a value for each Player.

What i'm trying to work out if its possible, is to extract this information and export it to a different tab in a table as a matrix. Hopefully that makes more sense once looking at the file.

 

[mumps]

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 that I follow. Could you please explain in detail what you mean by "results", the name of "another tab", where the "handicap" is found. Please use a few examples from your data referring to specific rows, columns, cells, ranges and sheets. It would also be helpful if you could upload a copy of your file which includes your expected results at least for a few players.

 

[Monty85]

I'm not sure that I follow. Could you please explain in detail what you mean by "results", the name of "another tab", where the "handicap" is found. Please use a few examples from your data referring to specific rows, columns, cells, ranges and sheets. It would also be helpful if you could upload a copy of your file which includes your expected results at least for a few players.

Apologies for the confusion.

I have reuploaded the file with some data in the expected results.

Dropbox

www.dropbox.com

To summarise;

How I currently calculate look up a players handicap.
Step 1: Select "Calculator" tab
Step 2: Select a Player Name is Cell C1
Step 3: Select a Handicap system in Cell C4
Step 4: Make a record of the result that is returned in Cell D4
Step 5 Repeat Steps 2-4 for each player name and each handicap system option
Step 6: Manually record the different player handicaps based on the different handicap systems in the table above

Expected Result
Instead of manually repeating the above steps to complete the table above
I would like to automate this so that the table is dynamically updated as things change

I have manually entered the expected results for the first 3 players

 

[mumps]

Copy and paste this macro into the worksheet code module. Do the following: right click the tab name for your Calculator sheet and click 'View Code'. Paste the macro into the empty code window that opens up. Close the code window to return to your sheet. The macro is triggered by a change in cell C4 so first select a player in C1 and then select a handicap in C4.

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("Expected Result")
    Set player = desWS.Range("A:A").Find(Range("C1").Value, LookIn:=xlValues, lookat:=xlWhole)
    If Not player Is Nothing Then
        Set handicap = desWS.Range("D2:H2").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

 

[Monty85]

Copy and paste this macro into the worksheet code module. Do the following: right click the tab name for your Calculator sheet and click 'View Code'. Paste the macro into the empty code window that opens up. Close the code window to return to your sheet. The macro is triggered by a change in cell C4 so first select a player in C1 and then select a handicap in C4.

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("Expected Result")
    Set player = desWS.Range("A:A").Find(Range("C1").Value, LookIn:=xlValues, lookat:=xlWhole)
    If Not player Is Nothing Then
        Set handicap = desWS.Range("D2:H2").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

I don't seem to be able to get this to work.

I've saved the file as a .xlsm and have pasted the code as suggest in the 'view code' menu for the calculator tab. But even after changing options in cell C4, nothing seems to flow through to the table in the "expected results" tab.

Thank you for your assistance with this as well - much appreciated.

 

[mumps]

It worked as you requested when I tested it on your sample file. Click here to download your file. Make a selection in C4. Make sure that you have enabled macros before you try it.

 

[Monty85]

It worked as you requested when I tested it on your sample file. Click here to download your file. Make a selection in C4. Make sure that you have enabled macros before you try it.

Thanks. This sort of works, however it seems to only extract one value at a time. So i still need to select every player and every option in cell C4 systematically to complete the table in 'expected results'.

At least, that's what it seems to be doing for me.

Ideally, i'd want to this complete the table in the expected results as a single process.

 

[mumps]

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))),"")