Dependent data validation

[parkjun]

Hi everyone,

I have data with list of departments. I want to create a dependent data validation for user choose to input information (exp list of values in center depends on value in division)
I have try formula Filter but it doest work in data validation or Indirect (not suitable with list of value has more than 20 values)

Can someone help me resolve this problem? Thank you.

 

[JamesCanale]

helper cells are needed (cols D and F below)

MrExcelPlayground23.xlsx
	A	B	C	D	E	F	G	H	I
1	Name	Dept		Departments				Dept:	A
2	John	A		A		John		Person:	Mary
3	Mary	A		B		Mary
4	Sally	B				Fred
5	Fred	A				Keith
6	Bill	B				Bo
7	Keith	A
8	Bo	A
Sheet4

Cell Formulas
Range	Formula
D2:D3	D2	=UNIQUE(Table3[Dept])
F2:F6	F2	=FILTER(Table3[Name],Table3[Dept]=I1)
Dynamic array formulas.

Cells with Data Validation
Cell	Allow	Criteria
I1	List	=D2#
I2	List	=F2#

 

[parkjun]

Thank you @JamesCanale, I tried this method but this one is only suitable for single input, it doest work if I want to apply for multi-row input

helper cells are needed (cols D and F below)

MrExcelPlayground23.xlsx
	A	B	C	D	E	F	G	H	I
1	Name	Dept		Departments				Dept:	A
2	John	A		A		John		Person:	Mary
3	Mary	A		B		Mary
4	Sally	B				Fred
5	Fred	A				Keith
6	Bill	B				Bo
7	Keith	A
8	Bo	A
Sheet4

Cell Formulas
Range	Formula
D2:D3	D2	=UNIQUE(Table3[Dept])
F2:F6	F2	=FILTER(Table3[Name],Table3[Dept]=I1)
Dynamic array formulas.

Cells with Data Validation
Cell	Allow	Criteria
I1	List	=D2#
I2	List	=F2#

 

[Akuini]

Since you're using Excel 365, please check out this tutorial from Mynda Treacy:
Easy Dynamic Dependent Data Validation Two Ways

 

[JamesCanale]

Maybe:

MrExcelPlayground23.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Name	Dept		Departments				Dept:	A	B
2	John	A		A		John		Person:	Mary
3	Mary	A		B		Mary
4	Sally	B		C		Sally
5	Fred	A		D		Fred
6	Bill	B				Bill
7	Keith	A				Keith
8	Bo	A				Bo
9	Person 1	C				Person 6
10	Person 2	C				Person 7
11	Person 3	C
12	Person 4	C
13	Person 5	D
14	Person 6	A
15	Person 7	B
16	Person 8	D
Sheet4

Cell Formulas
Range	Formula
D2:D5	D2	=UNIQUE(Table3[Dept])
F2:F10	F2	=FILTER(Table3[Name],ISNUMBER(XMATCH(Table3[Dept],I1:M1,0)))
Dynamic array formulas.

Cells with Data Validation
Cell	Allow	Criteria
I2	List	=F2#
I1:M1	List	=$D$2#