Problem with liked drop down lists

[phmalu]

Hi all,

I'm somewhat new to this linked drop down lists thing and I'm having a little problem with it.
I was able to make two dependent lists that work really nice when I'm first selecting the options as you can see in this first picture:

But then, when I change the value in the first list (parent) the old value previously selected in the second list (sibling) is kept even though it has not been registered as a valid option. I often end up with weird outcomes as the picture below shows:

What I want is that the dependent sibling list (cell G3) goes blank automatically or displays an error message whenever the parent main list (cell F3) is changed. How can I possibly do that?

Big thanks to all!

ps: I'm working with a rather complex data related to my area of study so I made this simple yet helpful example just to make things easier to understand.

 

[Yongle]

Some simple VBA can do what you want
- place in sheet module and save the workbook as macro enabled

Private Sub Worksheet_Change(ByVal Target As Range)
    With Target
        If .Row > 1 And .Column = [COLOR=#ff0000]3[/COLOR] Then .Offset(, 1).ClearContents
    End With
End Sub

Place code in sheet module?
Opens VBA window with {Alt}{F11}
Double-Click on correct sheet in Project Window
Paste code into window that opens
Go back to Excel with {Alt}{F11}

 

[Yongle]

If there are multiple dependent dropdown columns in the worksheet and example where dropdown is not in adjacent cell

Private Sub Worksheet_Change(ByVal Target As Range)
    With Target
        If .Row = 1 Then Exit Sub
        Select Case .Column
            Case 3, 6, 15           '[COLOR=#ff0000]columns C F O clear columns D G P[/COLOR]
                .Offset(, 1).ClearContents
            Case 20                 [COLOR=#ff0000]'change in column T [/COLOR][COLOR=#000080]clears column W[/COLOR]
                .Offset(, [COLOR=#000080]3[/COLOR]).ClearContents
        End Select
    End With
End Sub

same as above but using column letters

Private Sub Worksheet_Change(ByVal Target As Range)
    With Target
        If .Row = 1 Then Exit Sub
        Select Case .Column
            Case Columns("C").Column, Columns("F").Column, Columns("O").Column
                .Offset(, 1).ClearContents
            Case Columns("T").Column
                .Offset(, 3).ClearContents
        End Select
    End With
End Sub

 

[phmalu]

This is very helpful sir, but is there a way I can do what I need without using VBA? Just wondering because I'd rather use reg built-in formulas if I can.
Thank you!

 

[Yongle]

You have hit a major roadblock!

Excel formulas live in the "here and now" and are constantly refreshed every time a "source" value changes
But data validation is triggered only when the cell value is edited
Data validation can be via a formula but that does not affect the trigger
Formula cannot ERASE a value - it returns a value
Data Validation cannot ERASE a value - it rejects or accepts a value

Use another cell
The only thing you could do is to use another cell to warn if the pair of cells do not marry up
eg using your original data, put this formula in H3 and test what happens if F3 is changed
=IFERROR(IF(INDEX(C:C,MATCH(G3,D:D,0))<>F3,"Wrong Parent",""),"Sibling not Found")

You may think "Sibling not Found" is unnecessary
- MATCH would throw an error if a value in column D was corrected from "Sao Poalo" to "Sao Paolo" AFTER cell containing data validation was updated
- cell containing data validation would not warn you
- but formula in H3 would

 

[Yongle]

ooops I almost forgot about ..

OR
- use conditional formatting to highlight F3 if "wrong parent" and to highlight G3 if "sibling not found"

 

[phmalu]

Roger that, sir!

I think it's high time to learn and start using those VBA things then. Could you help me modify your base programming so it fits my data? I apologize in advance if I ask you "rookie" questions once this is the very first time I'm considering using VBA in any of my real sheets.

In my case, I have a few "key" cells linked to drop-downs and once those - and those only - are changed, a few other cells must go blank. Unfortunately in my case the "row" and "column" method you've described earlier won't work properly because it will affect another cells that must not be affected. e.g: I have linked drop-downs at B3 and C3 but I also have some random data in those same columns down below. If the macro is set to the entire column, that would mess up my data if I ever have to modify something there.

So I believe I need to specify the cells per se. e.g: when I change B3, both C3 and I15 must go blank; when I change C3, B3 can be kept but it must clear I15; when I change I15 both B3 and C3 can be kept.

Thank you once again!

 

[Yongle]

Test the code below which does what you requested in your example, and it is easy enough to add further cells
- it is part of your VBA training but you will not be using it (see below)
(delete previous code)

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rng As Range, tgt As String, del As String
    tgt = Target.Address(0, 0)
    Select Case tgt
        Case "B3":      del = "C3,I15"
        Case "C3":      del = "I15"
       [COLOR=#000080][I] 'add further cases here[/I][/COLOR]
    End Select
    
    If del <> "" Then
        Set rng = Range(del)
        rng.ClearContents
        MsgBox "Changing " & tgt & vbCr & "clears " & rng.Address(0, 0), vbExclamation, "Cells cleared"
    End If
End Sub

Why not using it?
- VBA is not Excel
- it behaves differently
- cells do not re-reference automaticaly
- Range("I15") is ALWAYS I15 even if a row is inserted above row 15
- in Excel I15 become I16 and every other formula adjusts
- add a row anywhere above row 15 and the code fails to work correctly
- add a row above row 3 and it fails to trigger at all
- with your "scatter" of cells it is highly likely that either a row or column will be inserted somewhere at some time and "break" the code

Solution
- use Named Ranges to create the required relationships
- Named Ranges adjust automatically if rows and columns are inserted

I am trying to work out the simplest way to do this and will post code later after testing
(travelling at the moment)

 

[Yongle]

Please read previous post FIRST and also test the previous code
- it will help you follow the logic in my thinking and to understand how this solution works

Method
- set up named range DD_B3 and DD_C3 to tell VBA which cells to delete when B3 /C3 amended
- VBA compares concatenation of "DD_" and "edited cell address" to see if there is a named range with that name and clear related cells
- the code allows you to cancel ClearContents - a message box tells you what is about to be deleted
- after testing remove both the IF Msgbox and its partner End If lines

1. Activate the sheet containing the dropdowns

2. Create Named Range with name DD_C3
- Click in RefersTo and delete the contents
- Click on cell I15
- RefersTo should be =SheetName!$I$15
- confirm with OK

3. Create Named Range with name DD_B3
- Click in RefersTo and delete the contents
- Hold down {Ctrl} and click on cells C3 and I15
- RefersTo should be =SheetName!$C$3,SheetName!$I$15
- confirm with OK

4. Replace VBA with code below and test

5. Add other Named Ranges to EXACTLY the same pattern

6. The code should not require further amendment for new Named Ranges

8. It is assumed that there are no other sheets in the workbook with dependent dropdowns that are "in the chain"
- if there are - please explain!!

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim nRng As Name, tgt As String
    tgt = Target.Address(0, 0)

    For Each nRng In ThisWorkbook.Names
        If nRng.Name = "DD_" & tgt Then
            If MsgBox("clear " & nRng.RefersToRange.Address(0, 0) & " ?", vbOKCancel) <> vbCancel Then
                nRng.RefersToRange.ClearContents
            End If
        End If
    Next
End Sub

If for any reason it is not working let me know
- am travelling and only intermittently looking at thread so may take me a day or two

 

[Yongle]

I should have made it clear that the code MUST be placed in the sheet module
- see post#2 for those instruction