Auto-clear dependent dropdown when parent cell is changed

Hello!

I have created some dependent drop downs that only give certain options when an item from the first column is selected. I'm wondering if there is a way to auto-clear the subsequent columns if the first column is changed? I have looked around a bit and have seen some VBA code stuff, but I'm an inexperienced user so it is all greek to me. Is there a way to do this with a formula within a cell?

I've attached a sample. Please don't laugh at my crude attempts!

Thanks and I hope to hear from you soon!

Kim

The only way is with VBA. It's not difficult if that is what you want to do. If what column (s) are changed, do you want the values (in which columns) cleared?

Hi Kim,
I did it in the attachment but here's how you do it. You want an Event driven macro (in this case, one that runs when you change something in G or H). This code would go in the worksheet module that you are using (in your example, sheet1). Right click on the sheet tab and "View Code" That opens the VBA editor. Paste this code in the text window (the code window)

Private Sub Worksheet_Change(ByVal Target As Range)

    If Not Intersect(Target, Range("H68:H118")) Is Nothing Then
        Target.Offset(0, 1).ClearContents
    ElseIf Not Intersect(Target, Range("G68:G118")) Is Nothing Then
        Target.Offset(0, 1).ClearContents
        Target.Offset(0, 2).ClearContents
    End If
End Sub

You'll need to modify the ranges if your spreadsheet is different. If you insert rows, unlike formulas, these will not auto adjust so you'll need to fix them.
Save the workbook with xlsm extention instead of xlsx.
You will need to "enable content" when you open this file.
Does this work for you?

Sorry I've realized I should have started a new post rather than replying to this one...

FYI, as an alternative to VBA, you could "fake it" with some conditional formatting. You could have the child cells turn their font to invisible colors when the value in that cell no longer matches a value from the selection in a parent cell.

Sometimes the change in the parent cell changes to a list that DOES still have the value in the child cell, so maybe that would be good to leave alone and visible. This method would do that, only hiding the values selected that are no longer accurate options.

Something to consider.

Okay, if you are going to have formulas in Column I, don't ever need that cleared. The code changes to

Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Target, Range("G68:G118")) Is Nothing Then
        Target.Offset(0, 1).ClearContents
End If
End Sub

Does that work for you?

Administrative Note:

Welcome to the forum.

We are happy to help, however whilst you feel your request is similar to this thread, experience has shown that things soon get confusing when answers refer to particular cells/ranges/sheets which are unique to your post and not relevant to the original.

Please see Forum Rule #1 about hijacking and start a new thread for your query.

If you are not familiar with how to start a new thread see the FAQ: How to start a new thread