dropdown list problem for newbie

i have a column(A) where each cell is a dropdownlist(same list). if a person chooses an item from that list...can it return a corresponding 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 the dropdown. i want it to return a value of 1174 in cell A1.

is this possible? if so please help?

thx
eric

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 wrote:
>
> i have a column(A) where each cell is a dropdownlist(same list). if a
> person chooses an item from that list...can it return a corresponding
> 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 the
> dropdown. i want it to return a value of 1174 in cell A1.
>
> is this possible? if so please help?
>
> thx
> eric
>
> --
> enags
> ------------------------------------------------------------------------
> enags's Profile: http://www.excelforum.com/member.php...o&userid=30948
> View this thread: http://www.excelforum.com/showthread...hreadid=506193

--

Dave Peterson

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 sheet1 column A where my dropdown list is(A1, A2, A3,...etc)?

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 wrote:
>
> 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 sheet1
> column A where my dropdown list is(A1, A2, A3,...etc)?
>
> --
> enags
> ------------------------------------------------------------------------
> enags's Profile: http://www.excelforum.com/member.php...o&userid=30948
> View this thread: http://www.excelforum.com/showthread...hreadid=506193

--

Dave Peterson

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 (the cell with the dropdown list)?

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

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 wrote:
>
> 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 (the
> cell with the dropdown list)?
>
> btw...thx for your prompt help...it is very much appreciated
>
> --
> enags
> ------------------------------------------------------------------------
> enags's Profile: http://www.excelforum.com/member.php...o&userid=30948
> View this thread: http://www.excelforum.com/showthread...hreadid=506193

--

Dave Peterson

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