Excel: Using different data validation lists depending on contents of another cell

Question

I'm creating a list in Excel.  In the three cells to the right of  each list item I want to choose from up to three dropdown lists of categories that the item's assigned to.

Data validation using lists handles this part just fine.  No problem so far.

But I want the list used in the second cell to change based on the choice made in the first cell and the list in the third cell to be based on the choice made in the second cell.

An example will make this clearer; this isn't actually what I'm doing, but the idea's the same:

First validation list contains:

Animal

Vegetable

Mineral

Now instead of including all possible choices in the second list, when the user chooses Vegetable, I'd like to include only:

Apple

Peach

Pear

and when they choose Apple, the third list should include only:

Winesap

Delicious

Gala

Similarly, choosing Animal would lead to options in the next cell of Lion, Tiger and Bear, with subcategories of the chosen animal in the third column as appropriate.

Otherwise, I'd have a huge number of inappropriate choices in the list for the second column and N-times that number of bad choices in the third column's validation list.

Suggestions?  TIA ...

Answer 1

If I am interpreting your question correctly then the following link explains how to create cascading/dependant dropdowns.

http://www.contextures.com/xlDataVal02.html

Answer 2

Brilliant ... that is *precisely* what I was hoping to find!

Thanks!