Seemingly simple dynamic drop down list question

[kurto55]

Hi all,

Hopefully there is a simple way of doing this, as I feel embarrassed that I have failed to this point.

A simple example would be a table with three columns - State, County, and City with 500 records (500 unique cities, 100 counties and 50 states)

I need thee drop downs, each based on the previous selection. I have created the unique state drop down, but what functions can I use to uniquely grab the counties that fall under that state and then the same for cities in the selected county.

Thank you for all the help!

 

[SteveO59L]

have a look at Aladins classic solution, here

https://www.mrexcel.com/forum/excel-questions/32-cascading-combo-boxes.html

 

[lrobbo314]

This seems to work. This code assumes that your data is in columns A:C. It also assumes that the dropdowns for State, County, and City are in E2, F2, and G2 respectively. You will need to adjust the code if any of your ranges are different.

Sub GetStates()
Dim AR()
Dim States()
Dim Res As String
Dim Dict As New Dictionary
Dim LR As Long


LR = Range("A" & Rows.Count).End(xlUp).Row()
States = Range("A2:A" & LR).Value


With Dict
    For i = 1 To UBound(States)
        If Not .Exists(States(i, 1)) Then .Add States(i, 1), States(i, 1)
    Next i
End With


ReDim AR(0 To Dict.Count - 1)


For j = 0 To UBound(AR)
    AR(j) = Dict.Items(j)
Next j


Res = Join(AR, ",")


With Range("E2").Validation
    .Delete
    .Add xlValidateList, xlValidAlertStop, xlBetween, Res
End With
End Sub




Sub GetCounties()
Dim AR()
Dim Counties()
Dim Res As String
Dim State As String
Dim Dict As New Dictionary
Dim LR As Long


State = Range("E2").Value
LR = Range("A" & Rows.Count).End(xlUp).Row()
Counties = Range("A2:B" & LR).Value


With Dict
    For i = 1 To UBound(Counties)
        If Counties(i, 1) = State Then
            If Not .Exists(Counties(i, 2)) Then .Add Counties(i, 2), Counties(i, 2)
        End If
    Next i
End With


ReDim AR(0 To Dict.Count - 1)


For j = 0 To UBound(AR)
    AR(j) = Dict.Items(j)
Next j


Res = Join(AR, ",")


With Range("F2").Validation
    .Delete
    .Add xlValidateList, xlValidAlertStop, xlBetween, Res
End With


End Sub


Sub GetCities()
Dim AR()
Dim Cities()
Dim Res As String
Dim State As String
Dim Dict As New Dictionary
Dim LR As Long


County = Range("F2").Value
LR = Range("A" & Rows.Count).End(xlUp).Row()
Cities = Range("B2:C" & LR).Value


With Dict
    For i = 1 To UBound(Cities)
        If Cities(i, 1) = County Then
            If Not .Exists(Cities(i, 2)) Then .Add Cities(i, 2), Cities(i, 2)
        End If
    Next i
End With


ReDim AR(0 To Dict.Count - 1)


For j = 0 To UBound(AR)
    AR(j) = Dict.Items(j)
Next j


Res = Join(AR, ",")


With Range("G2").Validation
    .Delete
    .Add xlValidateList, xlValidAlertStop, xlBetween, Res
End With


End Sub


Private Sub Worksheet_Activate()
GetStates
End Sub


Private Sub Worksheet_Change(ByVal Target As Range)
Select Case Target.Address
    Case "$E$2"
        GetCounties
    Case "$F$2"
        GetCities
End Select
End Sub

 

[kurto55]

Thank you both for the quick replies.

The named range way will not work, as I gave a simple example, but in realty there are four levels and the fourth level would have 400+ entries, thus having to create 400+ named ranges does not seem like the most ideal option.

The VBA code seems to be the way to go - I will give it a shot.

I was thinking since the four levels are in a four column table, there was a vlookup, offset, etc. solution that would generate the lists with some sort of nested functions inside the list via data validation?