dropdown list problem for newbie

[enags]

i have a column(A) where each cell is a dropdownlist(same list). if
person chooses an item from that list...can it return a correspondin
value?

example:
my drop down list corresponding value
animal 005
building 1174
vehicle 3345



so for a specific cell...lets say A1, i choose building from th
dropdown. i want it to return a value of 1174 in cell A1.

is this possible? if so please help?

thx
eri

 

[Dave Peterson]

I would use the adjacent column to show that corresponding value.

I'd use a formula like:

=if(a2="","",vlookup(a2,sheet2!a:b,2,false))

Assuming that you have that list on sheet2 in column A (and the corresponding
values in column B of that sheet2).

 

[enags]

yes....i have my lists on sheet2 column A and B.


so i would assign the formula
=if(a2="","",vlookup(a2,sheet2!a:b,2,false)) to each cell in sheet
column A where my dropdown list is(A1, A2, A3,...etc)

 

[Dave Peterson]

Put that formula in B2 -- right next to the cell with the dropdown.

Then copy it to B1 and B3, b4, ....

(I figured you might have headers in row 1.)

And Sheet1 A1:Axx contains the actual data|validation cells that use the list on
sheet2 (column A), right?

If yep, then I think you've got it.

If nope, then I'm confused.

 

[enags]

yes, sheet1 A2(A3,...etc) contains the data/validation cells.

so i applied the formula to sheet1 B2 and the returned value worked
however, is it possible to get that returned value onto sheet1 A2 (th
cell with the dropdown list)?

btw...thx for your prompt help...it is very much appreciated:

 

[Dave Peterson]

You could use the worksheet change event if you're using xl2k or higher.

Rightclick on the worksheet tab with the validation cells and select view code.
Paste this into the codewindow that opens:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

Dim Res As Variant
Dim myRng As Range

Set myRng = Worksheets("sheet2").Range("mylist")
If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Me.Range("a1:A9")) Is Nothing Then Exit Sub
If Target.Value = "" Then Exit Sub

On Error GoTo errHandler:

Res = Application.Match(Target.Value, myRng, 0)
If IsError(Res) Then
Beep 'something bad happened
Else
Application.EnableEvents = False
Target.Value = myRng(Res).Offset(0, 1).Value
End If

errHandler:
Application.EnableEvents = True

End Sub

Change that name of the list (I used myList). Change the range of the
data|validation cells (I used A1:A9).

===
Personally, I would use the extra cell. I think it would cause less confusion.

 

[enags]

dave...thx for the help...it was greatly appreciated