You could do it with data validation and the change worksheet event.

Assume your list is on sheet2, with the Data in A2:B100

your dropdown is on Sheet1 in cell B9

Create a named range

Insert=Name=Define

Name: Data1
Refersto: =OFFSET(Sheet2!$A$2,0,0,COUNTA(Sheet2!$A:$A)-1,1)

Select Sheet1:
Now go to B9 and do Data=Validation
select the list option
put in =Data1
(the name we created).

Now right click on the sheet tab and select view code.

Paste in this code:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng as Range, res as variant
On Error GoTo ErrHandler
If Target.Address = "$B$9" Then
Set rng = Worksheets("Sheet2").Range("Data1")
res = Application.Match(Target, rng, 0)
If Not IsError(res) Then
Application.EnableEvents = True
Target.Value = rng(res, 2)
End If
End If
ErrHandler:
Application.EnableEvents = True
End Sub

go back to sheet1 and select an item from the dropdown in B9.

--
Regards,
Tom Ogilvy


"twirth" wrote in message
...
Hello,
Does anyone know how I can make a dropdown list in Excel return a value
that
is not necessarily in the list?
Example - the list would include city names and possibly the corresponding
code, but I would like it to return only the corresponding three digit
"code".
Langdon City = 292
Munich City = 396
Hannah City = 442
I am assuming this is not possible just using the Data Validation feature
in
Excel, and I am more than willing to try it via VB, but I'm not familiar
enough with the code to know where to start!
Any help would be greatly appreciated!
Tanya