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!

 

[Marcelo Branco]

Maybe something like this

Create a table exactly like this and name it Table1

	E​	F​	G​
1​	Col1​	Col2​	Col3​
2​	Apples​	IPA​	Cotton​
3​	Oranges​	Pilsner​	Wool​
4​	Lemons​	Stout​	Nylon​
5​	Pears​	Lager​	Polyester​

Select A2 and in Data Valitadion > List insert this formula in Source
=INDIRECT("Table1[Col"&A1&"]")

Hope this helps

M.

 

[oldbrewer]

2

< < < cell A1

1

2

3

apples

ipa

rayon

oranges

pilsener

cotton

lemons

syout

wool

pears

lager

nylon

bitter

polyester

porter

row 16

1

ipa

#####

2

pilsener

3

syout

4

lager

5

bitter

6

porter

7

8

9

10

col J

#####

=IF(OFFSET($H$4,I16,MATCH($A$1,$I$4:$K$4,0))=0,"",OFFSET($H$4,I16,MATCH($A$1,$I$4:$K$4,0)))

J16:J25 should be named "list"

as A1 is changed, the list changes

<colgroup><col span="19"></colgroup><tbody>
</tbody>

 

[realtoast]

Still attempting.

 

[realtoast]

Maybe something like this

Create a table exactly like this and name it Table1

	E​	F​	G​
1​	Col1​	Col2​	Col3​
2​	Apples​	IPA​	Cotton​
3​	Oranges​	Pilsner​	Wool​
4​	Lemons​	Stout​	Nylon​
5​	Pears​	Lager​	Polyester​

<tbody>
</tbody>

Select A2 and in Data Valitadion > List insert this formula in Source
=INDIRECT("Table1[Col"&A1&"]")

Hope this helps

M.

I've followed your instructions. I end up with the error at Data Validation that says, "The Source currently evaluates to an error. Do you wish to continue?" I answer yes and end up with a dropdown list with no elements.

 

[Marcelo Branco]

I've followed your instructions. I end up with the error at Data Validation that says, "The Source currently evaluates to an error. Do you wish to continue?" I answer yes and end up with a dropdown list with no elements.

It worked perfectly for me.
Have you created a Table identical to what showed in post 2?
To do this: select the range; Home > Format as Table
Check if the table name is Table1 (the same used in the formula)

M.

 

[realtoast]

It worked perfectly for me.
Have you created a Table identical to what showed in post 2?
To do this: select the range; Home > Format as Table
Check if the table name is Table1 (the same used in the formula)

M.

There may be an issue with my Excel. I've also followed this excellent tutorial and I'm getting the same error. https://www.youtube.com/watch?v=rYdAsxf9EG0 (go to time 14:40). I'm going to try on a different computer.

Thank you!

 

[realtoast]

It worked perfectly for me.
Have you created a Table identical to what showed in post 2?
To do this: select the range; Home > Format as Table
Check if the table name is Table1 (the same used in the formula)

M.

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

I appreciate the help!

 

[Marcelo Branco]

You can download my file from
https://app.box.com/s/4nw03rbn6yh71y4uobppsw1cqmfz4k50

M.

 

[Marcelo Branco]

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

I appreciate the help!

You are welcome. Glad to help

M.