excel - clear dependent drop down, without using VBA

1
votes

I need help with clearing/changing a dependent drop down list in excel 2013, without using a macro, if it's possible. I've tried different workarounds, but nothing is working:

This is one of the things I tried:

=INDIRECT(VLOOKUP($A$8, lookuptable, 2, FALSE))

This doesn't work, the dependent (child) drop down cell is not being cleared/changed when the parent (A8) drop down is changed, causing a miss-match.

Thanks!

3
excelexcel-formula

3 Answers

1
votes

The technique for dependent data validation is to use range names for each dependent data list. The range name is the same as the value selected in the parent list. Example: the parent data validation cell has a list of countries. You also build several lists, one for each country, with the cities of that country. The range names for each list are the country names.

Apply the country DV to A1.

Then build another range name to use in the dependent data validation for the city cell, e.g. ListRange. The formula to build the range name is

=Indirect($A$1)

The dependent data validation then points to the range that has the same name as the value in cell A1. This is a dynamic range and will change when the selection in cell A1 changes.

This will not clear any existing choice in the dependent data validation cell. E.g, if your first DV cell has a list of countries and the dependent DV has a list of cities, selecting a country in A1 will change the DV list for the city cell and a city can be selected (France > Paris). But if the country cell is changed to Italy, the city cell will still show Paris, while the DV drop down now lists cities in Italy.

In order to clear the selected city when the country cell is changes, you will indeed need VBA.

0
votes

This is the best I have come up with, pick the country in cell A2 and the city will change BUT only according to the position specified in cell A4. I hope it may help you, cheers. auto chooser

-1
votes

I have a sample piece of code I use for a similar instance, one dropdown list with 3 options using an indirect formula to provide different choices per option.

I use named ranges and indirect formula via data validation.

When I change my list value in my dropdown cell "D2" in this example, it will then clear the second dropdown list in "F2" (2 columns to the right or offset by 2). This then enables the user to select from new choices dependant on the option change...
Hope this helps.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$D$2" Then
If Target.Validation.Type = "$F$2" Then
Application.EnableEvents = False
Target.Offset(0, 2).ClearContents
End If
End If
exitHandler:
Application.EnableEvents = True
Exit Sub
End Sub