Drop-down list to show other values?

[kxkxkxx]

I want to create a drop down list...easy enough...however, I want it to show a list of other values....for example....the drop-down list would have 4 choices, East, West, North, and South....if you choose say, "East"....I then want the 10 cells below it to display the values I have ready.

I am creating a ranking sheet....all my data is there, I just need to figure out how to show it.

Drop-down you choose "East"

This would then display below it:

[TABLE="width: 500"]
<tbody>[TR]
[TD][TABLE="width: 135"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]Customers[/TD]
[TD]Rank[/TD]
[/TR]
[TR]
[TD] NC [/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD] VA [/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD] MD [/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD] SC [/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD] FL [/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD] GA [/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD] NY [/TD]
[TD]7[/TD]
[/TR]
[TR]
[TD] NJ [/TD]
[TD]8[/TD]
[/TR]
[TR]
[TD] PA [/TD]
[TD]9[/TD]
[/TR]
[TR]
[TD] WV [/TD]
[TD]10[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Kamolga]

You can rather use a macro that copy your list and paste it if someone put East...or the cell below east (let's say A2, if list is in A1) you can use formula

=if(a1="East","NC","")

, in a3, same formula but VA instead of NC, etc.

 

[kxkxkxx]

You can rather use a macro that copy your list and paste it if someone put East...or the cell below east (let's say A2, if list is in A1) you can use formula

=if(a1="East","NC","")

, in a3, same formula but VA instead of NC, etc.

thank you!

 

[Eric W]

One way that's fairly easy to do uses the Name manager.

Excel 2010

	J	K	L	M	N	O	P
North	North	East	West	South
N1	N1	E1	W1	S1
N2	N2	E2	W2	S2
N3	N3	E3	W3	S3
N4	N4	E4	W4	S4
N5	N5	E5	W5	S5
N6	N6	E6	W6	S6
N7	N7	E7	W7	S7
N8	N8	E8	W8	S8
N9	N9	E9	W9	S9
N10	N10	E10	W10	S10

<tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet10​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: #DAE7F5"]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]J2:J11[/TH]
[TD="align: left"]{=INDIRECT(J1)}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]





Enter your lists in M:P as shown. Then select M2:M11, and in the Name box (to the left of the formula bar), type North (overtyping M2). Repeat for the other columns. Then in J1, select Data validation from the ribbon, pick List, and put the source as =M1:P1

Finally, select J2:J11. In the formula box, type:

=INDIRECT(J1)

and confirm it with Control-Shift-Enter.

The cells from J2:J11 will automatically populate with the chosen list when you select an option from the drop down list in J1. You can do this without Names, or the array function in J2:J11, but it's pretty basic this way.

Hope this helps!