search for data in a range of cells according to dropdown selection

  • Thread starter Thread starter Walter
  • Start date Start date
W

Walter

Sorry for the somewhat difficult description of the problem in the title,
but I'll try to explain my problem:

Let's say I have a range of cells A1:A3 and B1:B3 containing the following
text:

A1 contains the text "Cell 1"
A2 contains the text "Cell 2"
A3 contains the text "Cell 3"
B1 contains the text "Value 1"
B2 contains the text "Value 2"
B3 contains the text "Value 3"

I have also created an in-cell dropdown that displays 3 choices:
- Cell 1
- Cell 2
- Cell 3

Now, what I want to accomplish is the following:

When I select "Cell 1", I want Excel to search the range A1:A3 until it
finds "Cell 1". It would find "Cell 1" in A1.
Then it has to display the value of B1 in a cell of my choice.

The same for the other dropdown selections, so when I select "Cell 3", I
want Excel to search the range A1:A3 until it finds "Cell 3". It would find
"Cell 3" in A3.
Then it has to display the value of B3 in a cell of my choice.

I wanted to work with VBA Functions but I cannot find an event that is
triggered when a selection in an in-cell dropdown is changed.

I hope this is clear, I would appreciate any help!

Thanks

Greetings
Walter
 
Hi
if your drop down is in cell D1 enter the following in E1
=IF(D1<>"",VLOOKUP(D1,$A$1:$B$3,2,0),"")
 
I refer to the above mentioned problem posted and the answer provided b
Frank. My question is what if my drop down list was on the next shee
and not the same sheet as the "cell 1", "cell 2" and "cell 3". Wha
changes would I make to the formula?

Regards Stuar
 
Hi
just change the cell refeence. e.g.
=IF(D1<>"",VLOOKUP(D1,'sheet1'!$A$1:$B$3,2,0),"")
where D1 is in the current sheet and the list to search in sheet1
 
Back
Top