data validation against two values and lookup

[Lightkeepr]

I have two tables, one for time entry and the other lists pay amounts.

Book1
	A	B	C	D	E	F	G	H	I	J
2	Name	Code	Class	RT Hours	OT Hours	DT Hours	RT Pay	OT Pay	DT Pay	Total
3	Joe	001		20.0						$ -
4	Jane	002		10.0						$ -
Time

Cell Formulas
Range	Formula
J3:J4	J3	=([@[RT Hours]]*[@[RT Pay]])+([@[OT Hours]]*[@[OT Pay]])+([@[DT Hours]]*[@[DT Pay]])

Cells with Data Validation
Cell	Allow	Criteria
B3:B4	List	=codes

Book1
	A	B	C	D	E	F	G	H
3	CODE	Classification	RT	OT	DT		Unique Codes List
4	001	Boss	$ 10.00	$ 15.00	$ 20.00		001
5	002	Asst Boss	$ 9.00	$ 13.50	$ 18.00		002
6	002	Manager	$ 8.00	$ 12.00	$ 16.00		003
7	003	Employee	$ 7.00	$ 10.50	$ 14.00
8
Codes

Cell Formulas
Range	Formula
G4:G6	G4	=TEXT(UNIQUE(Table1[CODE],FALSE,FALSE),"000")
Dynamic array formulas.

On the first table I want to be able to enter a code in Code column that is data validated against the unique range in the second sheet (which currently works great). Where I am getting hung up is when it comes to the Class column on the first table. I want to have that be data validated against the codes table based on what code is in the previous cell.

So if Code 002 is used, in the Class column I only get 'Asst Boss' or 'Manager' as options to pick from.

Once both those values are picked, the rates for RT, OT and DT are used from the Codes table in the Time Table in their respective columns.

I thought about trying to use the filter function but couldn't get it to work in data validation rules and without building a bunch of other tables that would have to be manually tweaked if I added more to the Codes table, I am lost on what I am trying to do would be called to read up on it and learn how to solve this.

 

[Fluff]

One option if you are happy with helper cells.

+Fluff 1.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L
1	Name	Code	Class	RT Hours	OT Hours	DT Hours	RT Pay	OT Pay	DT Pay	Total
2	Joe	1		20							Boss
3	Jane	2		10							Asst Boss	Manager
4
5
6
7
8	CODE	Classification	RT	OT	DT		Unique Codes List
9	1	Boss	10	15	20		001
10	2	Asst Boss	9	13.5	18		002
11	2	Manager	8	12	16		003
12	3	Employee	7	10.5	14
13
14
Lists

Cell Formulas
Range	Formula
K2,K3:L3	K2	=TRANSPOSE(FILTER($B$9:$B$12,$A$9:$A$12=B2))
G9:G11	G9	=TEXT(UNIQUE(A9:A12),"000")
Dynamic array formulas.

Cells with Data Validation
Cell	Allow	Criteria
B2:B3	List	=$G$9#
C2:C3	List	=K2#

 

[Lightkeepr]

Thank you for the reply! I'm not entirely opposed to helpers but both sets of information being referred to or entered reside in tables and when I put the transpose-filter formula in off to the side of the time table, I get a #CALC! error 'Empty Arrays are not supported' and it wouldn't work too hot trying to incorporate it as a part of the table as some codes could have 5 classifications associated with it. Instead of direct Table references, I also tried named ranges for the same information and got the same error.

Book1.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L
2	Name	Code	Class	RT Hours	OT Hours	DT Hours	RT Pay	OT Pay	DT Pay	Total
3	Joe	001	#CALC!	20.0						$ -		#CALC!
4	Jane	002	#CALC!	10.0						$ -		#CALC!
5
Time

Cell Formulas
Range	Formula
C3:C4	C3	=L3#
J3:J4	J3	=([@[RT Hours]]*[@[RT Pay]])+([@[OT Hours]]*[@[OT Pay]])+([@[DT Hours]]*[@[DT Pay]])
L3:L4	L3	=TRANSPOSE(FILTER(Table1[Classification],Table1[CODE]=Table2[@Code]))

Cells with Data Validation
Cell	Allow	Criteria
B3:B4	List	=codes

 

[Fluff]

Make sure that both sets of "Codes" are the same, it looks as though table1 Codes are just numbers formatted to show leading zeros.
Also the formula you have put in C3 & C4 should be in the data validation, not the cell.