I have finally figured out how to build a dynamic cascading set of lists in Excel using Data Validation and the INDIRECT function. However, I'm running into a problem.
I would like to set up my spreadsheet so that whenever a change is made in a data validation list, all subsequent lists are set to a default value. For example:
Let's say I have List1, List2, List3, and List4. Each list's values are dependent on the selection made in the list before it. When I start the spreadsheet, I want all lists to default to "ALL". When the user selects a value in List1, List2 updates with a set of valid choices. I'd like to default List2 to one of those choices. However, while I can get List2 to populate with the correct set of selection options, I cannot get my desired default value assigned to it.
Similarly, if the user makes a selection in List1, List2, and List3, then goes back and changes List1, I'd like List2-List4 to default back to "ALL".
How do I do this?