11

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?

Journeyman Geek
  • 127,463
  • 52
  • 260
  • 430
Jay
  • 159
  • 1
  • 2
  • 6
  • 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? – Tamara Wijsman Apr 21 '12 at 08: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 08 '12 at 14:39
  • To illustrate both the VBA method and the CF method I mentioned above, there is a [sample file here](https://sites.google.com/a/madrocketscientist.com/jerrybeaucaires-excelassistant/system/app/pages/search?scope=search-site&q=dependent) _DependentLists3.xls_ – Jerry Beaucaire May 08 '12 at 14:45
  • @JerryBeaucaire: Could you post that as an answer? Or doesn't it fully answer the question? – Tamara Wijsman May 12 '12 at 15:15

1 Answers1

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

Jerry Beaucaire
  • 515
  • 2
  • 7