Feb 04 2023 02:06 AM
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
Feb 04 2023 02:44 AM
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.
Feb 04 2023 03:15 AM
@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.
Feb 04 2023 03:34 AM
Feb 04 2023 04:49 AM
Here is an example using a combo box and VBA. You'll have to allow macros.
Feb 04 2023 05:44 AM
@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
Feb 04 2023 08:37 AM - edited Feb 04 2023 08:43 AM
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