Depedent Drop Down Menus - 3 Columns

jkiser

New Member
Joined
May 7, 2008
Messages
8
Howdy!

I am having some problems with my dependent drop down menus through Data Validation.

My spreadsheet has Type (Fruit, Vegetable), Color, and Food.

Both of these are valid combinations:

Veg--> Green --> Celery
Fruit--> Green --> Pear

However, regular dependent drop downs mean the food is reliant only on the color.

I have researched and figured out how to force the user to choose only valid 3-way links by using the OFFSET and MATCH functionality and by looking in the set list sorted by type & color. However, what has happened is that when the user chooses color, the exact same color will be repeated for however many instances of food there are. So, Red will be repeated for Apple, Strawberry, etc.

I can attach or send the spreadsheet if anyone can help.

Jake
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

cornflakegirl

Well-known Member
Joined
Nov 4, 2004
Messages
2,023
I have researched and figured out how to force the user to choose only valid 3-way links by using the OFFSET and MATCH functionality and by looking in the set list sorted by type & color. However, what has happened is that when the user chooses color, the exact same color will be repeated for however many instances of food there are. So, Red will be repeated for Apple, Strawberry, etc.

I don't really get what you mean here. But I think I understand what you're trying to do in the first part of your question. What I would do is:

1. Create a Data Validation in A2 for Fruit/Vegetable
2. Create a named range called Fruit that contains possible fruit colours. Ditto Vegetable.
3. Create a Data Validation in B2 for Colour - formula = INDIRECT(A2)
4. Create a named range called FruitGreen that contains possible foods. Ditto FruitRed, Vegetable Green etc
5. Create a Data Validation in C2 for Food - formula = INDIRECT(A2&B2)

Does that help in any way?
 
Upvote 0

jkiser

New Member
Joined
May 7, 2008
Messages
8
I don't really get what you mean here. But I think I understand what you're trying to do in the first part of your question. What I would do is:

1. Create a Data Validation in A2 for Fruit/Vegetable
2. Create a named range called Fruit that contains possible fruit colours. Ditto Vegetable.
3. Create a Data Validation in B2 for Colour - formula = INDIRECT(A2)
4. Create a named range called FruitGreen that contains possible foods. Ditto FruitRed, Vegetable Green etc
5. Create a Data Validation in C2 for Food - formula = INDIRECT(A2&B2)

Does that help in any way?

Can you perform INDIRECTs for 2 variables?
 
Upvote 0

cornflakegirl

Well-known Member
Joined
Nov 4, 2004
Messages
2,023
In step 4 I've named the ranges based on concatenated results from the first two dropdowns. The data validation then creates a name in the same way, and INDIRECTs that.

Does that answer your question?
 
Upvote 0

jkiser

New Member
Joined
May 7, 2008
Messages
8
In step 4 I've named the ranges based on concatenated results from the first two dropdowns. The data validation then creates a name in the same way, and INDIRECTs that.

Does that answer your question?

PERFECT. Thanks.
 
Upvote 0

cornflakegirl

Well-known Member
Joined
Nov 4, 2004
Messages
2,023
Yes. Although I think I've seen a thread somewhere on here about how to do dependent dropdowns when it's not just a single word - might be worth a search if it's a thing for you.
 
Upvote 0

Forum statistics

Threads
1,190,565
Messages
5,981,707
Members
439,732
Latest member
TC_vii

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top