Depedent Drop Down Menus - 3 Columns

[jkiser]

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

 

[cornflakegirl]

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?

 

[jkiser]

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?

 

[cornflakegirl]

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?

 

[jkiser]

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.

 

[jkiser]

PERFECT. Thanks.

Although, this will only work if my categories are all single words, won't it?

 

[cornflakegirl]

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.