Drop down list

Question

Hello fellow expertise,

I have 2 set of dependent dropdown list. 1st dropdown is list of countries and 2nd drop down is the cities of the countries. For 2nd dropdown list the formula used is =INDIRECT(SUBSTITUTE(P2," ","_"))

The formula is working and when I selected Mexico, 2nd dropdown list shows Mexico City, Tijuana, Leon. (Table 1)

However, how to make value in 2nd dropdown list auto disappear when I deleted/unselected value in country . Refer Table 2

Table 1
Country	Mexico
City	Mexico City
Table 2
Country		Mexico deleted/unselected
City	Mexico City	Q: I want this cell auto blank when I deleted/unselect Mexico

Thank you for your kind help.

Answer 1

That requires a little bit of VBA code.

Right-click the sheet tab of the worksheet with the drop downs.

Select 'View Code' from the context menu,

Copy the following code into the worksheet module:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Range("P2"), Target) Is Nothing Then
        If Range("P2").Value = "" Then
            Application.ScreenUpdating = False
            Application.EnableEvents = False
            Range("P3").ClearContents
            Application.EnableEvents = True
            Application.ScreenUpdating = True
        End If
    End If
End Sub

Switch back to Excel.

Save the workbook as a macro-enabled workbook (*.xlsm).

Make sure that you allow macros when you open the workbook.