How do you do conditional data validation list boxes?

Posted by Hansoh on February 07, 2002 7:40 AM

here's a scenario.
2 data validation listboxes.

1st listbox has values: Animal, Plant, Mineral.

I want the 2nd listbox to be populated with certain values conditional upon user's choice in 1st listbox.

for example, if user chooses 'Animal' in listbox 1, i want the listbox values in 2 to be: dog, cat, fish. And if the user chooses 'Plant' in listbox 1, i want the listbox values in 2 to be: fern, palm tree, vine.

any ideas? is macro the only way to go here? or is there some clever way to manipulate data validation rules to achieve this? please advise. thanks in advance.

-han

Posted by Steve Hartman on February 07, 2002 8:01 AM

Name the ranges that contain the choices for each category with the name of the category ie. range B1:B3 containing dog, fish, and cat would be named animal.

In your second cell data validation where the choice is dependent on the first box choice choose List and for the range put =INDIRECT(A1) where A1 is the actuall cell where you made the first decision.