Cascading Dropdown using table with duplicates

[LaneBrewer]

Hi Everyone,

Below I have the following worksheet sample:

Book2.xlsx
	A	B	C	D	E	F	G	H
1	Name	Division	Center	JD			Select Name	Select Center
2	Adams, Stan	A	U	1			Dropdown of EmpName	Dropdown with Center(s) based on G2
3	Adams, Stan	A	V	1			Carson, Rita
4	Butler, Teddy	B	U	2
5	Butler, Teddy	B	V	2
6	Carson, Rita	C	X	3
7	Carson, Rita	C	Y	1
8	Day, Allison	A	Z	4
9	Evers, Leroy	C	U	1
10
11
12	EmpName	<--Name of Range (Removed Duplicates)
13	Adams, Stan
14	Butler, Teddy
15	Carson, Rita
16	Day, Allison
17	Evers, Leroy
Sheet1

Cells with Data Validation
Cell	Allow	Criteria
G2	Any value
G3	List	=EmpName

I am trying to figure out a formula to put into Data Validation that will then give me a dropdown that has all occurrences of Table1
for each EmpName chosen.

I have tried using =IFERROR(INDEX(GPTabPT,SMALL(IF(G3=EmpName,MATCH(ROW(EmpName),ROW(EmpName)),""),ROW($C1))),"")
and
=INDEX(GPTabPT,AGGREGATE(15,3,((EmpName=G3)/(EmpName=G3)*ROW(EmpName))-ROW(EmpName),ROWS(Sheet1!$B$2:Sheet1!B2)))
and
Offset() *I don't even remember what I tried but it didn't work.

I either get an error stating that I can't use arrays for Data Validation, the first value that matches, or I get no values in the dropdown. My actual table has over 100 unique values for EmpName so I don't want to have to create a named range for each to use the Indirect function


Excel Info:
Pro+ 2016
Version 2202 (Build 14931.20132 Click-to-Run)

Cheers,
Lane

 

[RasGhul]

Maybe this option, create these Named Ranges

EmpName

=Sheet1!$A$13#

Center

=Sheet1!$B$13#

Book1
	A	B	C	D	E	F	G	H	I
1	Name	Division	Center	JD			Select Name	Select Center
2	Adams, Stan	A	U	1			Carson, Rita	X
3	Adams, Stan	A	V	1
4	Butler, Teddy	B	U	2			Name	Division	JD
5	Butler, Teddy	B	V	2			Carson, Rita	C	3
6	Carson, Rita	C	X	3
7	Carson, Rita	C	Y	1
8	Day, Allison	A	Z	4
9	Evers, Leroy	C	U	1
10
11
12	EmpName	Center (G2)
13	Adams, Stan	X
14	Butler, Teddy	Y
15	Carson, Rita
16	Day, Allison
17	Evers, Leroy
Sheet1

Cell Formulas
Range	Formula
G5	G5	=FILTER(Table1[Name],(Table1[Name]=$G$2)*(Table1[Center]=$H$2),"")
H5	H5	=FILTER(Table1[Division],(Table1[Name]=$G$2)*(Table1[Center]=$H$2),"")
I5	I5	=FILTER(Table1[JD],(Table1[Name]=$G$2)*(Table1[Center]=$H$2),"")
A13:A17	A13	=SORT(UNIQUE(Table1[Name]))
B13:B14	B13	=FILTER(Table1[Center],Table1[Name]=G2)
Dynamic array formulas.

Cells with Data Validation
Cell	Allow	Criteria
G2	List	=Empname
H2	List	=Center

 

[LaneBrewer]

HI Ras,

Unfortunately my version of Excel does not include the FILTER() or SORT() functions nor will it allow me to use the # as you have shown. I've updated my sheet below to try and show better what I'm looking for.

Book2.xlsx
	A	B	C	D	E	F	G	H
1	Name	Division	Center	JD			Select Name	Select Center
2	Adams, Stan	A	U	1			Dropdown of EmpName	Dropdown with Center(s) based on G2
3	Adams, Stan	A	V	1			Carson, Rita
4	Butler, Teddy	B	U	2			Carson, Rita
5	Butler, Teddy	B	V	2			Butler, Teddy
6	Carson, Rita	C	X	3			Day, Allison
7	Carson, Rita	C	Y	1			Adams, Stan
8	Day, Allison	A	Z	4			Adams, Stan
9	Evers, Leroy	C	U	1			Adams, Stan
10
11
12	EmpName	<--Name of Range (Removed Duplicates)
13	Adams, Stan
14	Butler, Teddy
15	Carson, Rita
16	Day, Allison
17	Evers, Leroy
Sheet1

Cells with Data Validation
Cell	Allow	Criteria
G2	Any value
G3:G9	List	=EmpName
H3:H9	List	?

Thank you.

 

[Fluff]

my version of Excel does not include the FILTER() or SORT() functions

Are you sure that you are using xl 365 as per your profile?

 

[LaneBrewer]

Are you sure that you are using xl 365 as per your profile?

My apologies. My profile was not correct. I am using Professional Plus 2016 as stated in my original post.

 

[Fluff]

Thanks for updating your profile.

 

[Akuini]

My actual table has over 100 unique values for EmpName so I don't want to have to create a named range for each to use the Indirect function

If the problem is to create the named ranges then you can use macro to create it.
Try this:

Sub LaneBrewer1()

Dim i As Long, j As Long, n As Long
Dim va, tx As String

va = Range("A1:A9") 'Name
For i = 2 To UBound(va, 1) 'i = 2 because data start at row 2
    j = i
    Do
        i = i + 1
        If i > UBound(va, 1) Then Exit Do
    Loop While va(i, 1) = va(i - 1, 1)
    i = i - 1
        'replace space & comma with underscore
        tx = Replace(va(i, 1), " ", "_")
        tx = Replace(tx, ",", "_")

'    Debug.Print tx
    
    ActiveWorkbook.Names.Add Name:=tx, RefersTo:=Range(Cells(j, "C"), Cells(i, "C"))

Next

End Sub

It created the named ranges:

The code replaces space & comma with underscore because named range can't contain spaces and most punctuation characters. So the formula in the data-validation in H3 should be something like this:
=INDIRECT(SUBSTITUTE(SUBSTITUTE(G3," ","_"),",","_"))

 

[LaneBrewer]

If the problem is to create the named ranges then you can use macro to create it.
Try this:

Sub LaneBrewer1()

Dim i As Long, j As Long, n As Long
Dim va, tx As String

va = Range("A1:A9") 'Name
For i = 2 To UBound(va, 1) 'i = 2 because data start at row 2
    j = i
    Do
        i = i + 1
        If i > UBound(va, 1) Then Exit Do
    Loop While va(i, 1) = va(i - 1, 1)
    i = i - 1
        'replace space & comma with underscore
        tx = Replace(va(i, 1), " ", "_")
        tx = Replace(tx, ",", "_")

'    Debug.Print tx
   
    ActiveWorkbook.Names.Add Name:=tx, RefersTo:=Range(Cells(j, "C"), Cells(i, "C"))

Next

End Sub

It created the named ranges:
View attachment 61936

The code replaces space & comma with underscore because named range can't contain spaces and most punctuation characters. So the formula in the data-validation in H3 should be something like this:
=INDIRECT(SUBSTITUTE(SUBSTITUTE(G3," ","_"),",","_"))

Thank you for this Akuini. I was originally trying to do this without VBA but am starting to believe there is no other way with my version of Excel. This is an excellent solution. Thanks again.

 

[Akuini]

You're welcome, glad to help & thanks for the feedback.

 

[Akuini]

Since the list has over 100 unique values, you might want to have a searchable/autocomplete functionality on your data validation. You can try using a free add-in called "Search deList", here:

I created an Excel add-in called “Search deList”, to create searchable data validation

I created an add-in called “Search deList”, and I share it as a freeware. Its function is to speed up searching in data validation list. In any cell that has data validation (with List type) pressing ALT+RIGHT will open a Userform with a combobox. You can type multiple keywords (separated by a...

www.mrexcel.com