Archive of Mr Excel Message Board


Back to Data in Excel archive index
Back to archive home

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


Re: How do you do conditional data validation list boxes?

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.


This archive is from the original message board at www.MrExcel.com.
All contents © 1998-2004 MrExcel.com.
Visit our online store to buy searchable CD's with thousands of VBA and Excel answers.
Microsoft Excel is a registered trademark of the Microsoft Corporation.
MrExcel is a registered trademark of Tickling Keys, Inc.