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:

Customers Rank NC 1 VA 2 MD 3 SC 4 FL 5 GA 6 NY 7 NJ 8 PA 9 WV 10 <colgroup><col><col></colgroup><tbody> </tbody>

<tbody>
</tbody>

 

[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
1	North			North	East	West	South
2	N1			N1	E1	W1	S1
3	N2			N2	E2	W2	S2
4	N3			N3	E3	W3	S3
5	N4			N4	E4	W4	S4
6	N5			N5	E5	W5	S5
7	N6			N6	E6	W6	S6
8	N7			N7	E7	W7	S7
9	N8			N8	E8	W8	S8
10	N9			N9	E9	W9	S9
11	N10			N10	E10	W10	S10

<tbody>
</tbody>

Sheet10​

Array Formulas Cell Formula J2:J11 {=INDIRECT(J1)} <tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

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!