Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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?

share|improve this question
3  
This a job for a VBA worksheet_change event trigger. I don't have time right now to give the details (later I will if you still need help), but that's where you should direct your research. – Excellll Sep 10 '11 at 16:17
    
You have probably solved this by now or given up. I agree with Excellll that a worksheet change event might be the easiest. However, with the List option for Data Validation, the source can be an expression such as =IF(A1="List1",C1:C5,D1:D4). I have tested this with the value of cell A1 selected from a list and it does work in the way you seek. – Tony Dallimore Dec 22 '11 at 11:53
    
Are you still experiencing this problem? – Tom Wijsman Apr 21 '12 at 8:44
    
FYI, a nonVBA method I employ for this problem is to apply a CONDITIONAL formatting to the subsequent cells that checks if the current value it holds matches a value in the current INDIRECT() ref range. If NOT, then the cell turns red, or the value disappears (matching font/background) so the user knows the value for that cell must be updated/reselected. – Jerry Beaucaire May 8 '12 at 14:39
    
To illustrate both the VBA method and the CF method I mentioned above, there is a sample file here DependentLists3.xls – Jerry Beaucaire May 8 '12 at 14:45

A nonVBA method I employ for this problem is to apply a CONDITIONAL formatting to the subsequent cells that checks if the current value it holds matches a value in the current INDIRECT() ref range. If NOT, then the cell turns red, or the value disappears (matching font/background) so the user knows the value for that cell must be updated/reselected.

To illustrate this (and the VBA method mentioned by others above), there is a sample file here. The file you want is DependentLists3.xls

share|improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .