populate data based on multiple criteria

[zinah]

Hi,
I have below sample table that has different teams and managers, what I need to do is, when I select specific team and manager from the drop down menu, the categories list should be updated based on selected team and manager, can anyone help?
I have another challenge, each team has unique manager, i.e. Team1 has Manager1 and Manager2, I need the drop down list to populate only the managers from the selected team, any suggestion?

Here's the data:

Populate Data based on 2 criteria.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1	Team Name	Team1	Team4	Team3	Team2	Team1	Team2	Team4	Team2	Team2	Team3	Team3	Team3	Team2	Team4	Team4
2	Category/Manager	Manager1	Manager12	Manager10	Manager5	Manager2	Manager3	Manager13	Manager4	Manager6	Manager8	Manager9	Manager11	Manager7	Manager14	Manager15
3	Category1	33	55	51	54	60	38	56	53	61	46	51	54	57	62	49
4	Category2	18	45	43	42	53	13	48	45	55	37	51	47	50	57	41
5	Category3	36	58	50	68	55	43	50	29	53	37	44	48	55	48	55
6	Category4	50	71	72	87	79	35	75	61	82	67	64	75	71	81	78
7	Category5		46	32	41	46	30	38	37	49	27	32	33	54	34	34
8	Category6	42	59	58	50	66	53	58	50	61	41	53	59	63	67	50
9	Category7	26	54	51	43	62	44	56	77	63	49	52	53	58	62	52
10	Category8	26	44	44	33	53	21	54		53	42	50	47	47	64	32
11	Category9	29	45	36	40	43	23	42	24	46	34	42	37	40	50	34
12	Category10	38	58	59	64	63	38	68		50	52	52	58	55	75	63
13	Category11	33	50	43	50	55	50	59	70	59	39	59	54	52	65	38
14	Category12	60	81	78	88	87	80	79	100	96	75	73	90	81	84	65
15	Category13	-50	-25	-7	-20	11	-88	7	-40	-12	-21	-28	-14	-11	7	0
16
17
18
19		Team1
20
21		Manager1
22
23	Category1	33
24	Category2	18
25	Category3	36
26	Category4	50
27	Category5
28	Category6	42
29	Category7	26
30	Category8	26
31	Category9	29
32	Category10	38
33	Category11	33
34	Category12	60
35	Category13	-50
36
37
Sheet1

Cells with Data Validation
Cell	Allow	Criteria
B19:B20	List	=$B$1:$P$1
B21	List	=$B$2:$P$2

 

[Cubist]

How about.

=FILTER(B3:P15,(B1:P1=B19)*(B2:P2=B21),"")

 

[zinah]

How about.

=FILTER(B3:P15,(B1:P1=B19)*(B2:P2=B21),"")

Thanks for your help! How about a formula to populate all the managers under a selected team from the drop down list? Like below example, I selected Team4, then all the managers within this team should be populated?

Populate Data based on 2 criteria.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1	Team Name	Team1	Team4	Team3	Team2	Team1	Team2	Team4	Team2	Team2	Team3	Team3	Team3	Team2	Team4	Team4
2	Category/Manager	Manager1	Manager12	Manager10	Manager5	Manager2	Manager3	Manager13	Manager4	Manager6	Manager8	Manager9	Manager11	Manager7	Manager14	Manager15
3	Category1	33	55	51	54	60	38	56	53	61	46	51	54	57	62	49
4	Category2	18	45	43	42	53	13	48	45	55	37	51	47	50	57	41
5	Category3	36	58	50	68	55	43	50	29	53	37	44	48	55	48	55
6	Category4	50	71	72	87	79	35	75	61	82	67	64	75	71	81	78
7	Category5		46	32	41	46	30	38	37	49	27	32	33	54	34	34
8	Category6	42	59	58	50	66	53	58	50	61	41	53	59	63	67	50
9	Category7	26	54	51	43	62	44	56	77	63	49	52	53	58	62	52
10	Category8	26	44	44	33	53	21	54		53	42	50	47	47	64	32
11	Category9	29	45	36	40	43	23	42	24	46	34	42	37	40	50	34
12	Category10	38	58	59	64	63	38	68		50	52	52	58	55	75	63
13	Category11	33	50	43	50	55	50	59	70	59	39	59	54	52	65	38
14	Category12	60	81	78	88	87	80	79	100	96	75	73	90	81	84	65
15	Category13	-50	-25	-7	-20	11	-88	7	-40	-12	-21	-28	-14	-11	7	0
16
17
18
19		Team4
20
21
22		Manager12	Manager13	Manager14	Manager15
23	Category1	55	56	62	49
24	Category2	45	48	57	41
25	Category3	58	50	48	55
26	Category4	71	75	81	78
27	Category5	46	38	34	34
28	Category6	59	58	67	50
29	Category7	54	56	62	52
30	Category8	44	54	64	32
31	Category9	45	42	50	34
32	Category10	58	68	75	63
33	Category11	50	59	65	38
34	Category12	81	79	84	65
35	Category13	-25	7	7	0
36
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B1:P1	Cell Value	="Team4"	text	NO

Cells with Data Validation
Cell	Allow	Criteria
B19:B20	List	=$B$1:$P$1

 

[Cubist]

Try:

=FILTER(B2:P2,B1:P1=B19)

 

[zinah]

Try:

=FILTER(B2:P2,B1:P1=B19)

The formula gave me only the managers list, what I need is the manager list along with their results, for each category, just like the expected results table in my example below:

Populate Data based on 2 criteria.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1	Team Name	Team1	Team4	Team3	Team2	Team1	Team2	Team4	Team2	Team2	Team3	Team3	Team3	Team2	Team4	Team4
2	Category/Manager	Manager1	Manager12	Manager10	Manager5	Manager2	Manager3	Manager13	Manager4	Manager6	Manager8	Manager9	Manager11	Manager7	Manager14	Manager15
3	Category1	33	55	51	54	60	38	56	53	61	46	51	54	57	62	49
4	Category2	18	45	43	42	53	13	48	45	55	37	51	47	50	57	41
5	Category3	36	58	50	68	55	43	50	29	53	37	44	48	55	48	55
6	Category4	50	71	72	87	79	35	75	61	82	67	64	75	71	81	78
7	Category5		46	32	41	46	30	38	37	49	27	32	33	54	34	34
8	Category6	42	59	58	50	66	53	58	50	61	41	53	59	63	67	50
9	Category7	26	54	51	43	62	44	56	77	63	49	52	53	58	62	52
10	Category8	26	44	44	33	53	21	54		53	42	50	47	47	64	32
11	Category9	29	45	36	40	43	23	42	24	46	34	42	37	40	50	34
12	Category10	38	58	59	64	63	38	68		50	52	52	58	55	75	63
13	Category11	33	50	43	50	55	50	59	70	59	39	59	54	52	65	38
14	Category12	60	81	78	88	87	80	79	100	96	75	73	90	81	84	65
15	Category13	-50	-25	-7	-20	11	-88	7	-40	-12	-21	-28	-14	-11	7	0
16
17
18
19		Team4
20	Expected results
21
22		Manager12	Manager13	Manager14	Manager15		Manager12	Manager13	Manager14	Manager15
23	Category1	55	56	62	49
24	Category2	45	48	57	41
25	Category3	58	50	48	55
26	Category4	71	75	81	78
27	Category5	46	38	34	34
28	Category6	59	58	67	50
29	Category7	54	56	62	52
30	Category8	44	54	64	32
31	Category9	45	42	50	34
32	Category10	58	68	75	63
33	Category11	50	59	65	38
34	Category12	81	79	84	65
35	Category13	-25	7	7	0
36
Sheet1

Cell Formulas
Range	Formula
G22:J22	G22	=FILTER(B2:P2,B1:P1=B19)
Dynamic array formulas.

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B1:P1	Cell Value	="Team4"	text	NO

Cells with Data Validation
Cell	Allow	Criteria
B19:B20	List	=$B$1:$P$1

 

[Cubist]

Adjust the first range like this.

=FILTER(B2:P15,B1:P1=B19)

 

[zinah]

Adjust the first range like this.

=FILTER(B2:P15,B1:P1=B19)

THANK YOU!!!! You saved my time and manual sorting efforts

 

[Cubist]

You're welcome.