Macro or Formula to Clear Content of Interdependent Dropdown (indirect data validation)

[cherie109]

Hello,

I am trying to find a macro or a formula that will delete out interdependent dropdowns. I am a macro noob so thanks in advance for your patience.

Right now, I have a sheet that is set up using the indirect formula via data validation to allow the user to select various dropdowns. For example, if G30 is selected, then G32 will show the relevant dropdowns. This goes on for a number of rows (i.e., if G37 is selected, then G41 will show the relevant dropdowns, which will prompt G44, etc).

My issue is if any of the prior dropdowns are changed, the other related dropdowns stay static so that they are no longer valid. I saw on here that there was a way to do this via a macro to clear out related dropdowns but it is set up to do this when the dropdowns are next to each other. My excel is set up so that the dropdowns are stacked in the same column but different rows. Can someone help me modify the macro so that it works for drop-downs that are in the same column? Or is there a formula that will do the same thing?

For reference, the macro I found that does what I want (but is set up for cells in the same row) is:

Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
If Target.Column = 2 Then
If Target.Validation.Type = 3 Then
Application.EnableEvents = False
Target.Offset(0, 1).ClearContents
End If
End If

exitHandler:
Application.EnableEvents = True
Exit Sub

End Sub

 

[Yongle]

For example, if G30 is selected, then G32 will show the relevant dropdowns. This goes on for a number of rows (i.e., if G37 is selected, then G41 will show the relevant dropdowns, which will prompt G44, etc).

- I cannot see a pattern here

VBA needs to be told
1. Which cells to monitor for changes
2. AND which cells to clear if the cells being monitored are changed


If there is a simple relationship (eg the next cell down or to the right etc) then it's simply a matter of creating a rule that does what's required

If there is no pattern then that makes life more tricky

Is there a pattern? What is it?
- be specific
- perhaps there are several patterns to take account of etc

 

[cherie109]

-
Is there a pattern? What is it?
- be specific
- perhaps there are several patterns to take account of etc

Thanks for looking into this for me! There isn't an actual pattern but if VBA needs to have a pattern, I can make it so that it is every other row. For example, G30 is the first dropdown, then G32 is the related dropdown to G30, G34 is the related dropdown to G32 and so forth. Does that help?

So if I were to choose the province again and change it to Alberta, the "City" field still stays as Montreal. I am hoping to have a macro that will clear the State and City fields when I change the Province field.

 

[DanteAmor]

Try this.
Change "G30,G32,G34,G35,G39" to the related cells
It does not matter if they are one row or two rows or more rows down

Private Sub Worksheet_Change(ByVal Target As Range)
    Set Rng = Range("[COLOR=#ff0000]G30,G32,G34,G35,G39[/COLOR]")
    If Target.count > 1 Then Exit Sub
    If Not Intersect(Target, Rng) Is Nothing Then
        For Each c In Rng
            If c.Row > Target.Row Then
                Application.EnableEvents = False
                c.Value = ""
                Application.EnableEvents = True
            End If
        Next
    End If
End Sub

 

[cherie109]

IT WORKED!! Thank you so much @DanteAmor! YOU ARE A STAR! I cannot express my gratitude enough. Truly, thank you!

 

[DanteAmor]

I'm glad to help you. I appreciate your kind comments.