Multiple Cascading Drop Downs

[troygeri]

I have created cascading drop downs using the approach outlined here

http://www.contextures.com/xlDataVal02.html

I have the following

1. Concept Area (List 1)
2. Subject area within each concept area (List 2)
3. Entity within each subject area (List 3)

The only issue I have is if I say, reset the value in List 2, the values in list 1 and 3 only reset when I click on the cells in these lists, if I don't do this I am left with an invalid combination of values. So my question is whether there is a way to reset these other lists as soon as I have changed the value in list 2, so that the value resets / defaults to the first item in the drop down of list 1 and 3 (which I will set to <please select a value>, without me needing to click on those cells ?

Thanks - if necessary I can send a copy of the file I am working on

 

[mikerickson]

You could put code like this in the sheet's code module.

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim oneCell As Range
    On Error GoTo ErrorOut
    For Each oneCell In ActiveSheet.Cells.SpecialCells(xlCellTypeAllValidation)
        If Not oneCell.Validation.Value Then oneCell.ClearContents
    Next oneCell
ErrorOut:
Application.EnableEvents = True
On Error GoTo 0
End Sub

One note: If your dependent named range evaluate to an error, this code will not clear the cell that draws from that range. Make sure that if the precedent cell is blank, the named range evaluates to something, not an error.

 

[troygeri]

Thanks - seems to work nicely by clearing the cell to the right when cell drop down value is changed, the only thing is when list 2 is reset it needs to clear the cell to the left (list 1) and to the right (list 3)

 

[troygeri]

Actually - ignore my last comment, if i reset list 2, it can only be to another value within the list 1 value selected so no need to clear list 1 on a reset of the value in list 2. The only improvement is if value in List 1 is reset that it clears both columns to the right (i.e. list 2 and 3). Even though 3 resets when you update blank value in list 2 to new value, in the interim you are left with -

1. new value in list 1, (correct)
2. Blank in <BLANK>list 2 (correct)
3. Value in list 3 which was linked to prev cleared valued from list 2<VALUE value 2 3 cleared been now has which list in old the on dependent was that>. (improve to below) - i.e. make blank

So if we can just get the two columns to the right to clear on reset of list 1 / col 1 - the result will be perfect

 

[mikerickson]

The code does not clear the subordinate cells, it check to make sure that all cells are consistant with the dependent lists.

Lets say that the user selects "a" from a list for A1.
they then choose "b" from the (dependent) list for B1
Then then choose "c" from the list for C1

User then selects "aa" from the list for A1.
"b" is not on the "aa" dependent list, so the code blanks B1
If "c" is on the dependent list for blank, then "aa",blank,"c" is allowed by the rules set up by the validation, and the "c" is left alone.

That code does not blank dependent cells, unless the rules set up by the definitions of the dependencies require it.

If you want to insure that all downstream entries are blanked, define the named ranges so that (to return to the above example) a blank in B1 will change the dependent list for C1 to be a blank cell.

List3 RefersTo: =IF(B1="","",currentformula)

is one way to achieve this (If the ignore Blanks box is unchecked)

or, if that box has to be checked, put =CHAR(5) in cell AZ10000
then the formula =IF(B1="",AZ10000,currentformula) will do the trick.

 

[troygeri]

Thanks. The approach seemed to work (i.e. =if(B2="","",currentformula). However when I try and use the same formula in subsequent rows and then change the value in column A seems to hang the system and CPU flies up to 50% on excel which seems to indicate some sort of infinite loop...

 

[troygeri]

Maybe it would help to look at the file and see what I mean by the file freezing when I change values in the list. Link to download file