Data Validation Question

[stwpkcmo]

Quick question about data validation dropdowns . . . Is it possible to assign the dropdown fields unique values, but have them display a different text message.
For instance, when I develop in VSNET I can create a dropdown list that will read:
Yes
No
Maybe So

But I can assign each selection a custom numeric value, for instance:
Yes = 1
No = 2
Maybe So = 3


Is this possible in excel, without using VBA?

Thanks for the help!

 

[villy]

Without using VBA, I don't know but,
You can try this. change the ranges to suit your data

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
With Range("A10"
        Validation.Delete
        With .Validation
            .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
            xlBetween, Formula1:="=$B$1:$B$5"
        End With
Select Case Range("A10")
    Case "Yes"
        .Value = 1
    Case "No"
        .Value = 2
    Case "Maybe"
        .Value = 3
    Case "I don't know"
        .Value = -1
    Case "Not sure"
        .Value = 0
End Select
End With
End Sub

 

[stwpkcmo]

Thanks villy . . .

I know how to handle the situation with VBA, but that unfortunately is not an option in this instance.

Thanks for the help anyhow

 

[RoryA]

Not with a DV dropdown, no, but you can with a Forms dropdown which by default returns the index number of the selected item, not its text.