Conditional Data Validation?

[AndrewS]

Hello,

I'm a bit stumped. I'm sure there's a way to do this but I'm not sure how.

I'm building a matrix. In my sample file, column B has "Type", and column C has Food. Column B is data validated with dropdown menus for the subsequent food categories, and column C is where I need my conditional options to appear.

Is there a way to make it so if a food type is selected in the "Type" dropdown for column B, the next cell in the row (in Column C's "Food" section) will populate a dropdown of those food types?

The second tab contains all the data for foods according to their types, but I'd like it to get it to be picked up conditionally by the Type selected on the first tab's B column and then be available in a dropdown from the next cell over (in C column).

Example: if I choose Citrus under the Types column (B11), how can I make (C11) display a dropdown menu of the corresponding items on the second tab (in this case Oranges and Grapefruit), including any items I may add to that list in the future?

 

[tomas]

http://chandoo.org/forum/threads/multi-data-list-validation.22916/

I think this is similar situation to yours

 

[Luke M]

Hi Andrew,

Check out some of Chandoo's articles on dynamic data validation:
http://chandoo.org/wp/2010/09/13/dynamic-data-validation-excel/
and more recently:
http://chandoo.org/wp/2014/02/13/dynamic-cascading-dropdowns-that-reset/

 

[rrocker1405]

Hi Andrews,

Welcome to Chandoo.org.

You could use the indirect function to list values based on the criterion as required. In the attached sheet refer to sheet 4 to use drop-down's. The base information of the drop-down values could be found in Sheet 3.

You could find more detailed information about indirect function in the below link.

http://www.contextures.com/xlFunctions05.html
Kind regards,
Anand