Dependent Drop Downs

[RaviWildcat]

Hi - this should be a very easy task but I've been spinning my wheels for 30 minutes so I'm posting on mrexcel.

I'm creating a sign in sheet. On another tab I have a list of names

Last_Name First_Name
Brady Greg
Brady Marsha
Cunningham Richard
Cunningham Joanie

On my sign in sheet I want to pull up last names, and then I want to pull up valid first names based on the last name.

So, under the last name, I want a dropdown that lets me choose Brady or Cunningham.

If I select Brady

under the first name I want a dropdown that lets me choose either Greg or Marsha

I can get the first drop down ok. I'm struggling with the dependent drop down based upon the first value.

(I tried creating a name called Brady and then using indirect for the first name but that hasn't worked sadly)

 

[RustamMika]

Hi RaviWildcat
If your list of names started from cell("A1") in list named "another tab"
try this
whrere Private Sub Worksheet_Change(ByVal Target As Range)- event for your sign sheet and last name drop down cell is "F1"
The first name dropdown will generate in "G1"

Private Sub Worksheet_Change(ByVal Target As Range)
    Set nms = CreateObject("System.Collections.ArrayList")
    If Not Intersect(Target, Range("F1")) Is Nothing Then
        nms.Clear
        With Worksheets("another tab")
            lur = .Cells(Rows.Count, 1).End(xlUp).Row
            For i = 2 To lur
                If .Cells(i, 1).Value = [f1].Value Then
                    If Not nms.Contains(.Cells(i, 2)) Then .Add .Cells(i, 2)
                End If
            Next
        End With
        nms.Sort
        first_names = Join(nms.toarray, ",")
        With [G1].Validation
            .Delete
            .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:=first_names
        End With
    End If
End Sub

 

[RaviWildcat]

Thank you! I will investigate!

 

[kevin9999]

With 365, I would just use the inbuilt functions to achieve this without the need for VBA. Note the formulas in D2 & E2, and the validation lists in G2 & H2.

Book1
	A	B	C	D	E	F	G	H
1	Last Name	First Name		Last Unique	First Unique		Last Name Choice	First Name Choice
2	Brady	Greg		Brady	Greg		Brady	Greg
3	Brady	Marsha		Cunningham	Marsha
4	Cunningham	Richie
5	Cunningham	Joanie
Sheet1

Cell Formulas
Range	Formula
D2:D3	D2	=UNIQUE(A2:A5)
E2:E3	E2	=FILTER(B2:B5,A2:A5=G2,"")
Dynamic array formulas.

Cells with Data Validation
Cell	Allow	Criteria
G2	List	=$D$2#
H2	List	=$E$2#

Book1
	A	B	C	D	E	F	G	H
1	Last Name	First Name		Last Unique	First Unique		Last Name Choice	First Name Choice
2	Brady	Greg		Brady	Richie		Cunningham	Joanie
3	Brady	Marsha		Cunningham	Joanie
4	Cunningham	Richie
5	Cunningham	Joanie
6
Sheet1

Cell Formulas
Range	Formula
D2:D3	D2	=UNIQUE(A2:A5)
E2:E3	E2	=FILTER(B2:B5,A2:A5=G2,"")
Dynamic array formulas.

Cells with Data Validation
Cell	Allow	Criteria
G2	List	=$D$2#
H2	List	=$E$2#

 

[RaviWildcat]

With 365, I would just use the inbuilt functions to achieve this without the need for VBA. Note the formulas in D2 & E2, and the validation lists in G2 & H2.

Book1
	A	B	C	D	E	F	G	H
1	Last Name	First Name		Last Unique	First Unique		Last Name Choice	First Name Choice
2	Brady	Greg		Brady	Greg		Brady	Greg
3	Brady	Marsha		Cunningham	Marsha
4	Cunningham	Richie
5	Cunningham	Joanie
Sheet1

Cell Formulas
Range	Formula
D2:D3	D2	=UNIQUE(A2:A5)
E2:E3	E2	=FILTER(B2:B5,A2:A5=G2,"")
Dynamic array formulas.

Cells with Data Validation
Cell	Allow	Criteria
G2	List	=$D$2#
H2	List	=$E$2#

Book1
	A	B	C	D	E	F	G	H
1	Last Name	First Name		Last Unique	First Unique		Last Name Choice	First Name Choice
2	Brady	Greg		Brady	Richie		Cunningham	Joanie
3	Brady	Marsha		Cunningham	Joanie
4	Cunningham	Richie
5	Cunningham	Joanie
6
Sheet1

Cell Formulas
Range	Formula
D2:D3	D2	=UNIQUE(A2:A5)
E2:E3	E2	=FILTER(B2:B5,A2:A5=G2,"")
Dynamic array formulas.

Cells with Data Validation
Cell	Allow	Criteria
G2	List	=$D$2#
H2	List	=$E$2#

What an elegant approach! Thank you!

 

[kevin9999]

Glad to help & thanks for the feedback