Best Way To Substitute Text From Drop Down List?

I have a list of approximately 100 products that I would like to give users a dropdown list to select from BUT while I want the list to show the product name I need the value placed into the cell to be its 5 to 7 character product code.

Seems like something that should be possible but would really appreciate some direction in how to go about it.

Thanks,

Possible you could adapt this excellent look up approach from Jerry Beaucaire

Autocomplete Data Validation

Download the sample file and give it a test drive.

If VBA is viable you can use a Change Event on the DV cells to perform the lookup upon alteration and override the cell result
(edit: this approach takes advantage of one of DV's flaws - a pasted value is not "validated")

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rngI As Range, rngC As Range, vTemp As Variant
    On Error GoTo ExitPoint
    Application.EnableEvents = False
    Set rngI = Intersect(Target, Range("D1:D10"))
    If Not rngI Is Nothing Then
        For Each rngC In rngI.Cells
            With rngC
                If .Value <> "" Then
                    vTemp = Application.VLookup(.Value, Range("A1:B10"), 2, 0)
                    .Value = IIf(IsError(vTemp), .Value, vTemp)
                End If
            End With
        Next rngC
    End If
ExitPoint:
    Set rngI = Nothing
    Application.EnableEvents = True
End Sub

the above is a very simplistic example but basically states that DV cells are D1:D10 and that DV source lookup table is A1:B10 where A1:A10 hold the values sourcing the DV list itself and B1:B10 represent the codes to be returned to the cell upon selection.