I saved this from a previous post:
Personally, I'd use two cells. The cell with data|validation would contain the
long string and the adjacent(?) cell would contain the short string
Put the table in Sheet2 (long name in column A, short name in column B).
A1 has the data|validation cell
B1 has this formula:
=if(a1="","",vlookup(a1,sheet2!a:b,2,false)
Then I could use either cell in any other formulas.
But if you're using xl2k or higher (won't work in xl97), you could use the same
table and a worksheet_event that overwrites the value in the cell with the short
name.
You'll have to name that range in column A for Data|Validation to work with a
list on a different worksheet.
Debra Dalgleish explains it:
http://contextures.com/xlDataVal01.html#Name
If you want to try...
I used Sheet2 and created a list named myList.
Then right click on the worksheet tab that should have this behavior and select
view code. And paste this into the code window:
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim res As Variant
Dim myList As Range
If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Me.Range("a1")) Is Nothing Then
Exit Sub
End If
If Target.Value = "" Then Exit Sub
Set myList = Worksheets("sheet2").Range("myList")
res = Application.Match(Target.Value, myList, 0)
If IsError(res) Then
'this shouldn't happen!
MsgBox "Something bad happened"
Else
Application.EnableEvents = False
Target.Value = myList(res, 2).Value
Application.EnableEvents = True
End If
End Sub
Then change "a1" to the cell that has the data|validation.
And back to excel to test it.