Inserting Formula into Conditional Formatting Function in excel

Question

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, 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!

[Final Result Should show this]

How do you build the Paired-up table? If with INDEX-MATCH than you can use the same logic for the format condition - returning the value for "paired" instead of "score" - than if equals to 1 = true — Ike
– Ike, Commented May 22, 2022 at 8:40
Do the row and column numbers 1-8 actually appear along the side and top of your paired-up table? If so, it makes answering the question a bit easier. — Tom Sharpe
– Tom Sharpe, Commented May 22, 2022 at 8:42
Hi lke. I built the paired-up table by directly typing in the number. I got the result of the "Score" column by using the INDEX function. — Nappy
– Nappy, Commented May 22, 2022 at 9:47
Hi Tom. Do the row and column numbers 1-8 actually appear along the side and top of your paired-up table? - Yes it does. — Nappy
– Nappy, Commented May 22, 2022 at 9:48

Evil Blue Monkey · Accepted Answer · 2022-05-22 10:45:30Z

1

Since there can be only one combination of BoyA and BoyB for each score/pair, you can use a SUMIFS formula to easily find the match you are looking for (both to report the score data and to highlights the paired ones). A formula like this should do the trick:

=SUMIFS( Paired_Column , BoyA_Column , Ordinate_Value , BoyB_Column , Abscissa_Value )

Assuming a situation like this:

the formula you are looking for should be like this:

=SUMIFS($N:$N,$K:$K,$A3,$L:$L,B$2)

edited May 22, 2022 at 10:45

answered May 22, 2022 at 10:40

Evil Blue Monkey

2,8791 gold badge10 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Inserting Formula into Conditional Formatting Function in excel

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related