Multiple dependency data validation list

[metcala]

Hi all

I am looking to create a dependent data validation list that takes type and category and returns a list of corresponding valid tasks.

The sheet is structured as below with type only completed in B1 and a new row for each Category and Task.

Data Entry

Type	...
Category	Task
...	...
...	...

Lookup

Type	Category	Task
...	...	...

I have the data validation working for Type and Category but for Task I can only get it so it returns corresponding Tasks based on Category not Type and Category.

This is as far as I've got...

=OFFSET('Lookup'!$B$1,MATCH($A4,'Lookup'!$B:$B,0)-1,0,COUNTIF('Lookup'!$B:$B,$A4),1)

Any help would be very much appreciated!

 

[JamesCanale]

Not at all sure this is helpful...

MrExcelPlayground23.xlsx
	A	B	C	D	E	F	G	H	I	J
1	Lookup Sheet								Data Entry
2	Type	Category	Task		A	1	Drive		Type:	D
3	A	1	Run		B	2	Steal		Category	2
4	A	2	Jump		C	3
5	B	3	Hide		D	4			Tasks:
6	B	1	Walk						Drive
7	C	2	Slide						Steal
8	C	3	Read
9	C	1	Write
10	C	2	Fight
11	C	3	Flee
12	D	1	Ride
13	D	2	Drive
14	D	3	Rove
15	D	4	Hit
16	D	1	Bunt
17	D	2	Steal
Sheet11

Cell Formulas
Range	Formula
E2:F5	E2	=UNIQUE(A3:A17)
G2:G3	G2	=FILTER(C3:C17,(A3:A17=J2)*(B3:B17=J3))
Dynamic array formulas.

Cells with Data Validation
Cell	Allow	Criteria
J2	List	=E2#
J3	List	=F2#
I6:I11	List	=$G$2#