Select an item from dropdown list but return value from adjacent c

[Guest]

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?

 

[T. Valko]

Try this:

A2:A10 = items
B2:B10 = ID numbers

Create a drop down list of the items:

Select cell E2
Goto the menu Data>Validation
Allow: List
Source: =A2:A10
OK

Enter this formula in cell F2:

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

Biff

 

[Guest]

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]

I know this was possible in Lotus 1-2-3, but can't find a similar function

in Excel.

There is none. The only way this would be possible is to use VBA code. I
can't help you with that.

Biff

 

[Dave Peterson]

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.