Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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
show 1 more comment

1 Answer 1

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
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.