Dropdown List with "description" and actual "value"

[sandrosg]

Hi there, 

I'm facing an issue with a dropdown list/menu. I need to create a simple KM calculation. However the KM(value) need to show its description (characters) in the dropdown menu. Any chance via the Data->Validation->List option? I'm enclosing 2 screens and a simple workbook. 

Thanks for your response 

[#2]

@sandrosg 

A simple workaround: display a data validation input message:

Result:

Otherwise, you'd either need a more complicated sum formula, or an ActiveX combo box together with VBA code.

[#3]

@sandrosg Another work-around could be to use TEXTJOIN to create the drop down list as "A to B; 20" etc., as shown in G6:G8,

And then you can use a not too complicated formula (G13) to SUM the values from the selected tracks.

[#4]

Hi Riny, this comes pretty close to my challenge. Is there any way to display just the numeric values in 1 cell ?I guess then it's getting more complex...anyway thx so far...

[#5]

@sandrosg 

Here is an example using a combo box and VBA. You'll have to allow macros.

[#6]

@Hans Vogelaar Dear Hans, thx for the example...however on my Excel for Mac it doesnt work due to the missing Active X support...gonna try that later under W11.. Thx Sandro 

[#7]

@sandrosg 

Here's another option, using vba & data validation:
Helper range: $G$6:$G$8.

Data validation formula: =$G$6:$G$8.
On the Error Alert tab, uncheck mark on "Show error alert after invalid data is entered".
In Sheet1 code window, put this code:

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo skip:

If Target.Cells.CountLarge > 1 Then Exit Sub
    If Len(Target) = 0 Then Exit Sub
    
    If Not Intersect(Target, Range("F14:F16")) Is Nothing Then 'Range("F14:F16") is range with data validation
        Dim tx As String, c As Range, v
            tx = Target.Value
            If Len(tx) > 0 Then
            
                If InStr(tx, " : ") Then
                    v = Split(tx, " : ")(1)
                    Else
                    v = Target.Value
                End If
                Application.EnableEvents = False
                'Range("F6:F8") is col Distance
                Set c = Range("F6:F8").Find(What:=v, LookIn:=xlValues, lookAt:=xlWhole, MatchCase:=False, SearchFormat:=False)
                    If Not c Is Nothing Then
                        Target = v
                    Else
                        MsgBox "Wrong Entry"
                        Target.ClearContents
                    End If
               Application.EnableEvents = True
               
            End If
 
    End If

Exit Sub
skip:
Application.EnableEvents = True
MsgBox "Error number " & Err.Number & " : " & Err.Description
End Sub