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.


StevanT wrote:

Biff,

Thanks for the quick response. Not exactly what I was after. What I wanted
to do is (using your example), select an item in cell E2, and then return the
required ID Number into E2, not into F2.
I know this was possible in Lotus 1-2-3, but can't find a similar function
in Excel.
I appreciate your help.

"T. Valko" wrote:

Try this:

A2:A10 = items
B2:B10 = ID numbers

Create a drop down list of the items:

Select cell E2
Goto the menu DataValidation
Allow: List
Source: =A2:A10
OK

Enter this formula in cell F2:

=IF(E2="","",VLOOKUP(E2,A2:B10,2,0))

Biff

"StevanT" wrote in message
...
What I would like to do is have an array of data in columns, the first
column
being the Item, the second being the ID Number, etc. In a cell, I would
like
to see a list of Items from which I can select, but once selected, I want
to
return the ID Number into the cell. Is this possible?




--

Dave Peterson