how to have a drop down box option, once selected then have another cell display a series of data?

[excelnubie]

I have a drop down box with a range to select from.

Let's say my data range (in the drop down box) is A1:A5
A1: A
A2: B
A3: C
A4: D
A5: E

If I select A1, I want cell C1 to display what is listed in B1, but if I select A2, I want C1 to display what's listed in B2 and so on.

So long as I select something, but regardless of what drop down option I do select, I always want it to prompt display of data in cell C1 (from data in another place). Basically I have a LOT of data, and so I want to shortcut the review time, and just have the drop down box force cell C1 to show what the drop down box option relates to.

Is there way to do this?

I use MS Excel 2000 on Windows XP, and am reasonably ok with formulas, but not that good with scripts.

 

[crimson_b1ade]

in Cell C1 put the formula =VLOOKUP(Yourdropdowncell,A:B, 2,false)

 

[excelnubie]

ok that didn't seem to work - perhaps I need to re-explain. Let's assume this is what my sheet looks like:

A1: A B1: test C1:
A2: B B2: word
A3: C B3: cats
A4: D B4: dogs
A5: E B5: fish

A6: drop down box (that has A1:A5 data range in it, as options).

In Cell A6, I drop down the box to option "C".
I want cell C1 to now display "cats"

If I changed Cell A6 to drop down box option "D", I would want C1 to now automatically display B4.

Thus whatever I change the drop down box to, I want it to correspond automatically to another cells data, and return that function by displaying a result in cell C1.

Hopefully that makes things clearer?

 

[Andrew Poulsom]

In C1:

=VLOOKUP(A6,A1:B5,2,FALSE)

 

[excelnubie]

ok - that worked for column B. Whatever drop down selection I choose, it returns the result.

Let's now assume the following:

A1=A B1=test C1=east
A2=B B2=word C2=west
A3=C B3=cats C3=north
A4=D B4=dogs C4=south

In cell A5 I have a drop down box containing options A1:A4. Say I select option "B", so now cell A5 is displaying "B". I want Cell B5 to display "word" and I also want cell C5 to display west. (I actually have data in columns b so would want cells D5:05 to also display corresponding row "2" data, if my drop down selection were A2).

Therefore if I select in cell A5 option "C", cell B5 should show "cats", cell C5 should show "north", etc....

Is it possible to have the vlookup function look across the row, and return the data from each row into each separate column?

At the moment, in cell B5, I have =VLOOKUP(A5,A1:B4,2,FALSE)which is working fine. But if I copy that formula into cell C5 as well, then the data returned in cell C5 is identical to B5 - whereas I want it to be different.

 

[excelnubie]

I think I got it.

I changed =VLOOKUP(A5,A1:B4,2,FALSE) in B5 to:
=VLOOKUP(A5,A1:C4,3,FALSE) in C5 and it seems to work ok.

Let me know if I'm missing anything?

 

[crimson_b1ade]

nope. that looks fine.