Dependent drop down list excel

Question

I have a question about how to create a drop down column that is based on previous column. What I am struggling with is how my data is structure.

My first column A has all the country names. The header of that column is named Country. The second Column(column B) has all the city names. The header of that column is named Cities. I want to be able to select a country and then the next column should only show cities for that country for me to select.

My approach I try to use name range for my 2 columns. Then I go to the next sheet and create 2 columns(CountryInput, CitiesInput). In the column named CountryInput I go into data validation tools to create the first drop down list. The column CitiesInput which should depend on CountryInput is what I am having problem with. I tried to use indirect=A2 function but nothing happens.

Also can I use vba or macro to do this?

A search for "excel cascading drop down menus" should yields several results on how to do this — cybernetic.nomad
But it seems like your data need to be structured a certain way — G.Aurelien

Hansraj Hansraj · Accepted Answer · 2019-01-16T02:51:07

Yes it is possible to use VBA to do this. However named ranges are possible only if you intend to use countries and cities as a contiguous list i.e. all rows sorted on countries and cities in that order. The below code will allow you to create this feature irrespective of the sorting order, i.e. even if data is not sorted. This is a basic code not written for performance but works, please edit accordingly . Hope this solves your problem.

Sub SetupCountry() 'run this on workbook open event
    Dim rng As Range
    Set rng = ActiveSheet.Range("H7")  'choose your cell(s) here
    With rng.Validation
        FRM = GetUniqueCountries()
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:=FRM
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = ""
        .ErrorMessage = ""
        .ShowInput = True
        .ShowError = True
    End With
End Sub

Sub SetupCity()  'run this sub on the change event of Country cell
    Dim rng As Range
    Set rng = ActiveSheet.Range("I7")  'choose your cell(s) here
    With rng.Validation
        FRM = GetCities()
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:=FRM
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = ""
        .ErrorMessage = ""
        .ShowInput = True
        .ShowError = True
    End With
End Sub


Function GetUniqueCountries() As String
    Dim sOut As String
    Dim v, c
    Dim rngList As Range

    Set rngList = ActiveSheet.Range("D7:D28") 'edit the range where your country list is stored
    sOut = ""

    For Each c In rngList
        If InStr(1, sOut, c.Value & ",") = 0 Then  'check if the value is already in the upload list and add if not there
            sOut = c.Value & "," & sOut
        End If
    Next c
    'remove first ,
    If sOut <> "" Then
        sOut = Left(sOut, Len(sOut) - 1)
    End If
    GetUniqueCountries = sOut
End Function
Function GetCities() As String
     Dim sOut As String
    Dim v, c
    Dim rngSearch As Range

    Set rngSearch = ActiveSheet.Range("D7:D28") 'edit the range where your cities list exists
    sOut = ""

    For Each c In rngSearch
        If c.Value = ActiveSheet.Range("H7").Value Then 'selected country
            sOut = sOut & "," & ActiveSheet.Range("E" & c.Row).Value
        End If
    Next c
    'remove first ,
    If sOut <> "" Then
        sOut = Mid(sOut, 2)
    End If
    GetCities = sOut
End Function

If you are ok sorting the the data along country and cities then named ranges would be a more elegant solution. Where the datavalidation formula for city would then refer to a named range e.g. CITIES you would need to reset the range for CITIES based on value of country ( using a similar construct of getCities() function.

one simple way to change the range reference for a named range is show below. The formula can be updated based on the search output.


ActiveWorkbook.Names("SOMENAMEDRANGE").RefersTo = "=Sheet1!$D$5:$L$25"

Dependent drop down list excel

1 Answers