Excel Data Validation List - Different display data to result data

Question

I'm not sure if Excel can do this, but here is hoping! I have data that looks like this: A column with Full Names and a column with Initials

What I am wanting to do is have a dropdown list, where the options you see and can choose from are the Full Names, but the data that is entered into the cell is the Initials.

I can't even begin to think what I would need to do. I've done VLOOKUP things where you change one thing with the drop down in one cell, and the data of the cell next to it changes, but I am looking for this to be in the same cell.

Answer 1

Yes, you can do this with just a normal list/validation + VBA.

1. set up your validation values (name list). Pair with swaps (shortened names)

2. set up your data entry table

3. Data -> Validate using List

4. For the sheet, attach VBA (my brief test below)

   1. watches the correct column on the data entry table

   2. on event, swaps out the entry value with the replacement value

The only catch is that your VBA will trigger itself: the function changes the cell value, which triggers a cell change event, which triggers the same function, etc. Below, I break that loop by setting a flag, but there are probably other solutions.

EDIT: The cells in the image show a data validation flag. You can auto-clear that by adding one more instruction after setting the new value. I've updated the code snippet below.

Public DisabledFlag As Boolean

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim KeyCells As Range
    Dim NewValue As Variant
    
If DisabledFlag = True Then
    DisabledFlag = False
    GoTo Disabled
End If


' The variable KeyCells contains the cells that will
    ' cause an alert when they are changed.
    Set KeyCells = Range("D4:D12")

If Not Application.Intersect(KeyCells, Range(Target.Address)) _
    Is Nothing Then
    
    ' Use the current value to get the 'real' value ...
    On Error Resume Next
    NewValue = WorksheetFunction.XLookup( _
        Target.Value, _
        Range("A4:A6"), _
        Range("B4:B6"), _
        "Not found")
    On Error GoTo 0

    
    ' Setting the value will trigger this function ...
    ' Ignore that event.
    DisabledFlag = True
    Target.Value = NewValue

    ' Clear the data validation error message (the replacement value
    ' is not in the actual validation set, so Excel flags the cell)
    Target.Errors(xlListDataValidation).Ignore = True

    End If

Disabled:

End Sub