Data Validation

[Stephen_IV]

I have been trying to research this but can not get anything to work. I am trying to use a data validation list that shows 1 thing but puts another thing in the cell.

For instance I would like to see A in the validation list but when I click A the cell value shows 111.

Hope this makes sense.

For example:
A = 111
B = 222
C = 333
D = 444
E = 555

 

[Eric W]

Data Validation doesn't work that way. But you can put a formula in the cell next to the DV cell that does a lookup function to get the converted value.

 

[Stephen_IV]

Eric,

Any way to do something like this in code.

 

[Eric W]

Yes, if you want to go that route. Open a copy of your workbook. On the sheet with the DV cell on it, right click the sheet tab and select View Code. Paste this code in the window that opens:

Sub worksheet_Change(ByVal target As Range)


    If Intersect(target, Range("F1")) Is Nothing Then Exit Sub
    Application.EnableEvents = False
    Select Case target.Value
        Case "A": target.Value = 111
        Case "B": target.Value = 222
        Case "C": target.Value = 333
        Case "D": target.Value = 444
        Case "E": target.Value = 555
    End Select
    Application.EnableEvents = True
End Sub

Change the F1 to the cell where your DV is. Press Alt-Q to close the editor. Try it out.

 

[Stephen_IV]

Ahhhhhh!! Very Nice!!!! Thank you again!

 

[Eric W]

Happy to help!