How to create a drop down list but show different values in Excel 2016

Question

I created a drop down list, but I want it to function so that when you select from it, it displays something else.  For example: If the dropdown list contained the names of the states, I would want to have the drop down list show the full name, but when you select it, I want the abbreviation to be displayed.  Select New York, but have NY be displayed.  I have created the two lists but I can't figure out how to get the substitution to work.  I'm afraid what seems simple may be very complicated. Thoughts?

< moved from: Office/Excel /Windows other /Office 2016 >

Answer 1

You can do it seamlessly with a Change event. The following assumes your validation is in cell A1, and you have a table, called tbl_States. As soon as you change A2, the code will look for its matching value one column to the right with VLOOKUP, and replace it. It will work the same with Employee Name/#, etc.

Private Sub Worksheet_Change(ByVal Target As Range)

    '   Code goes in the Worksheet specific module

    Dim rng As Range

    Dim strAbbv As String

    Dim rngStates As Range

        '   Set Target Range, i.e. Range("A1, B2, C3"), or Range("A1:B3")

        Set rng = Target.Parent.Range("A2")

        Set rngStates = Range("tbl_States")

        '   Only look at single cell changes

            If Target.Count > 1 Then Exit Sub

            '   Only look at that range

            If Intersect(Target, rng) Is Nothing Then Exit Sub

            '   Action if Condition(s) are met (do your thing here...)

            If LenB(Target.Value) > 0 Then

                strAbbv = Application.WorksheetFunction.VLookup(Target.Value, rngStates, 2, False)

                Application.EnableEvents = False

                    Target.Value = strAbbv

                Application.EnableEvents = True

            End If

End Sub