Multiple-column Drop-down

[mburr88]

I'm not sure if what I'm trying to do is even possible, but if anyone could
figure out how to do it, that would be great!
Here's what I want to happen: I want one column (say, B) to have a drop-down
list of things, say W, X, Y, Z. Then, when you choose an option, say W, I
want pre-set values to appear in columns C, D, E, etc.
For example, if you pick W in the drop-down list in column B, then in column
C, the number 1 would appear, and in D, 2, E, 3, etc. (So it would look like:
W 1 2 3 ...)
Or if you pick X, it might be: X 5 6 7 ....
Y might be: Y 3 7 2 ....
etc

Is it possible to make this happen? If so, how??? In the simplest terms,
please
Thank you so much!!

 

[L. Howard Kittle]

Yes you can do that.

Do this on a blank worksheet to get the concept.

IN B1 do your drop down list of W, X, Y, Z.
In column H1:H4 enter W, X, Y, Z.
In I1:K1 enter 1, 2, 3.
In I2:K2 enter 4, 5, 6.
In I3:K3 enter 7, 8, 9.
In I4:K4 enter 10, 11, 12.

Now select C1:E1, and while STILL selected type in this LOOKUP formula:

VLOOKUP(B1,H1:K4,{2,3,4},0)

Now use CTRL + SHIFT + ENTER to commit. (It is an ARRAY formula, Excel will
put curly brackets { } around the formulas, don't do this yourself.)

Change the selection in the B1 drop down to see the different returns in C,
D and E.

If your data changes and you need to alter the vlookup formula, you will
need to reselect ALL three cells containing the formula (C1:E1) then modify
the formula and re-commit with CTRL + SHIFT + ENTER.

If you want to delete the formula you will also have to select all three
cells containing the ARRAY formula and hit delete.

HTH
Regards,
Howard

 

[grizzly6969]

http://www.contextures.on.ca/xlDataVal13.html

this will help if still not sure ---- reply