Dynamic dropdown list contents based on another cell's value

[realtoast]

I would like to create a dropdown list with elements that are based on the value of another cell (note that I do know how to create dropdown lists).

For example:

A1 is the master cell, A2 holds the dropdown list and selected value

A1 = 1, A2 list: Apples, Oranges, Lemons, Pears
A1 = 2, A2 list: IPA, Pilsner, Stout, Lager
A1 = 3, A2 list: Cotton, Wool, Nylon, Polyester
etc.

I am not proficient with VBA, so I'm hoping there's a way to do this using formulas. I did find this helpful article http://excelsemipro.com/2011/05/a-dynamic-dependent-drop-down-list-in-excel/, which I was able to mimic. But I've not figured out how to modify this process to use the master cell instead of the defined name category.

Any help appreciated!

 

[realtoast]

Marcelo, thank you very much! I was able to get your suggestion to work!

I appreciate the help!

In case anyone else comes across this, my error was due to not understanding that, in the Data Validation Source formula, =INDIRECT("Table1[Col"&A1&"]"), "Col" was referencing the actual column name (thought it was an Excel property) and I had different header names in my table. Once I corrected the header names to Col1, Col2, etc. the dropdown list worked as intended.

 

[Marcelo Branco]

In case anyone else comes across this, my error was due to not understanding that, in the Data Validation Source formula, =INDIRECT("Table1[Col"&A1&"]"), "Col" was referencing the actual column name (thought it was an Excel property) and I had different header names in my table. Once I corrected the header names to Col1, Col2, etc. the dropdown list worked as intended.

Yes, in a table you should use the reference to column name.
An alternative solution (better) referring the column position, instead of column name, would be something like this
1. Create a named range, with no headers, containing the 3 lists and name it, say, MyLists like below

[TABLE="class: grid"]
<tbody>[TR]
[TD="bgcolor: #DCE6F1"][/TD]
[TD="bgcolor: #DCE6F1"]

I​

[/TD]
[TD="bgcolor: #DCE6F1"]

J​

[/TD]
[TD="bgcolor: #DCE6F1"]

K​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

1​

[/TD]
[TD]

Apples​

[/TD]
[TD]

IPA​

[/TD]
[TD]

Cotton​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

2​

[/TD]
[TD]

Oranges​

[/TD]
[TD]

Pilsner​

[/TD]
[TD]

Wool​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

3​

[/TD]
[TD]

Lemons​

[/TD]
[TD]

Stout​

[/TD]
[TD]

Nylon​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

4​

[/TD]
[TD]

Pears​

[/TD]
[TD]

Lager​

[/TD]
[TD]

Polyester​

[/TD]
[/TR]
</tbody>[/TABLE]


And use the formula below in Data Validation > List > Source
=INDEX(MyLists,0,$A$1)

M.

 

[sscornav]

I followed the instructions and it worked well. My columns are named "A" "B" but they are of different lengths. So "A" would have "Apple", "Ant", "B" would have "Bat", "Bump", "Banana", "Bag". So there are a few blanks at the bottom of list A. When the cell is with the dropdown is blank, it starts at the blanks space. Kind of annoying....any way to fix this?

 

[Marcelo Branco]

Maybe something like this

Assumes data in Sheet1


[Table="class: grid"][tr][td="bgcolor: #DCE6F1"][/td][td="bgcolor: #DCE6F1"]

A​

[/td][td="bgcolor: #DCE6F1"]

B​

[/td][td="bgcolor: #DCE6F1"]

C​

[/td][td="bgcolor: #DCE6F1"]

D​

[/td][td="bgcolor: #DCE6F1"]

E​

[/td][/tr]
[tr][td="bgcolor: #DCE6F1"]

1​

[/td][td]

1​

[/td][td][/td][td][/td][td]

A​

[/td][td]

B​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

2​

[/td][td]

Apple​

[/td][td][/td][td][/td][td]

Apple​

[/td][td]

Bat​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

3​

[/td][td][/td][td][/td][td][/td][td]

Ant​

[/td][td]

Bump​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

4​

[/td][td][/td][td][/td][td][/td][td][/td][td]

Banana​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

5​

[/td][td][/td][td][/td][td][/td][td][/td][td]

Bag​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

6​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]

Named Range
D1:E5 --> MyLists

Create a named formula
Go to: Formulas > Names Manager > New
Name: MyRange
Refers to: =INDEX(MyLists,1,Sheet1!$A$1):INDEX(MyLists,COUNTA(INDEX(MyLists,0,Sheet1!$A$1)),Sheet1!$A$1)

Then select A2 and in Data Validation > List insert this formula
=MyRange

M.