Inserting Formula into Conditional Formatting Function in excel

[Napzz]

Hi everyone. I am trying to conditional format the **Paired-up Table** according to the result of the Paired column.


The "BoyA" column reflects the Row number of the Paired-up Table, the "BoyB" column reflects the column number of the Paired-up Table.

For example: If the value in the **Paired** column is 1, I would like to highlight the cell in the Paired-up Table according to the BoyA and BoyB.

Since the Paired column shows the value of 1 when BoyA=1 and BoyB=5, I have to highlight the value 2 from Row 1 and Column 5 of the Paired-up Table, and so on and so forth.

I have tried many different ways by tying the formula into the conditional formatting function of excel but in vain. Thank you!

 

[Napzz]

6random.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
5		1	2	3	4	5	6	7	8		BoyA	BoyB	Score	Paired
6	1		9	3	4	2	1	5	7		1	2	9	0
7	2			4	5	6	7	8	3		1	3	3	0
8	3				4	2	8	3	5		1	4	4	0
9	4					3	2	4	6		1	5	2	1
10	5						8	7	6		1	6	1	0
11	6							2	3		1	7	5	0
12	7								4		1	8	7	0
13	8										2	3	4	0
14											2	4	5	0
15											2	5	6	0
16											2	6	7	0
17											2	7	8	0
18											2	8	3	1
19											3	4	4	0
20											3	5	2	0
21											3	6	8	0
22											3	7	3	1
23											3	8	5	0
24											4	5	3	0
25											4	6	2	1
26											4	7	4	0
27											4	8	6	0
28											5	6	8	0
29											5	7	7	0
30											5	8	6	0
31											6	7	2	0
32											6	8	3	0
33											7	8	4	0
Sheet1

Cell Formulas
Range	Formula
M6:M33	M6	=INDEX($B$6:$I$13,K6,L6)

Named Ranges
Name	Refers To	Cells
BoyA	=Sheet1!$K$6:$K$33	M6
BoyB	=Sheet1!$L$6:$L$33	M6

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
N6:N33	Cell Value	=1	text	NO

 

[Napzz]

Just uploaded the mini-sheet to here. Would appreciate your response!

 

[Napzz]

Anyone, please?

 

[Peter_SSs]

Welcome to the MrExcel board!

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Is this what you mean?

22 05 22.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
5		1	2	3	4	5	6	7	8		BoyA	BoyB	Score	Paired
6	1		9	3	4	2	1	5	7		1	2	9	0
7	2			4	5	6	7	8	3		1	3	3	0
8	3				4	2	8	3	5		1	4	4	0
9	4					3	2	4	6		1	5	2	1
10	5						8	7	6		1	6	1	0
11	6							2	3		1	7	5	0
12	7								4		1	8	7	0
13	8										2	3	4	0
14											2	4	5	0
15											2	5	6	0
16											2	6	7	0
17											2	7	8	0
18											2	8	3	1
19											3	4	4	0
20											3	5	2	0
21											3	6	8	0
22											3	7	3	1
23											3	8	5	0
24											4	5	3	0
25											4	6	2	1
26											4	7	4	0
27											4	8	6	0
28											5	6	8	0
29											5	7	7	0
30											5	8	6	0
31											6	7	2	0
32											6	8	3	0
33											7	8	4	0
CF

Cell Formulas
Range	Formula
M6:M33	M6	=INDEX($B$6:$I$13,K6,L6)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B6:I13	Expression	=COUNTIFS($K$6:$K$33,$A6,$L$6:$L$33,B$5,$N$6:$N$33,1)	text	NO

 

[Napzz]

Welcome to the MrExcel board!

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Is this what you mean?

22 05 22.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
5		1	2	3	4	5	6	7	8		BoyA	BoyB	Score	Paired
6	1		9	3	4	2	1	5	7		1	2	9	0
7	2			4	5	6	7	8	3		1	3	3	0
8	3				4	2	8	3	5		1	4	4	0
9	4					3	2	4	6		1	5	2	1
10	5						8	7	6		1	6	1	0
11	6							2	3		1	7	5	0
12	7								4		1	8	7	0
13	8										2	3	4	0
14											2	4	5	0
15											2	5	6	0
16											2	6	7	0
17											2	7	8	0
18											2	8	3	1
19											3	4	4	0
20											3	5	2	0
21											3	6	8	0
22											3	7	3	1
23											3	8	5	0
24											4	5	3	0
25											4	6	2	1
26											4	7	4	0
27											4	8	6	0
28											5	6	8	0
29											5	7	7	0
30											5	8	6	0
31											6	7	2	0
32											6	8	3	0
33											7	8	4	0
CF

Cell Formulas
Range	Formula
M6:M33	M6	=INDEX($B$6:$I$13,K6,L6)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B6:I13	Expression	=COUNTIFS($K$6:$K$33,$A6,$L$6:$L$33,B$5,$N$6:$N$33,1)	text	NO

Hi Peter. Thank you so much for your response.
Your formula makes sense to me but I am not sure why it does not work on my side. (It shows an error message)
What can I do with this situation?

I am currently using MacOS with the latest version of Excel.
Thanks!

 

[Peter_SSs]

I am currently using MacOS with the latest version of Excel.

Please put that information in your Account details as suggested so the information is always readily available to helpers.

(It shows an error message)

What error message?

In your Excel do you normally use commas or semicolons to separate function arguments?

 

[Napzz]

Please put that information in your Account details as suggested so the information is always readily available to helpers.


What error message?

In your Excel do you normally use commas or semicolons to separate function arguments?

Solved! Thank you for the suggestions and answers

 

[Peter_SSs]

Solved! Thank you for the suggestions and answers

Good news and you are welcome.

.. but what about updating your account details still?