CODEWITHSUNDEEP
excelexcel-formula
Nappy
Nappy

Reputation: 11

Inserting Formula into Conditional Formatting Function in excel

enter image description hereI 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.Final Result

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]

Upvotes: 0

Views: 65

Answers (1)

Evil Blue Monkey
Evil Blue Monkey

Reputation: 2619

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: enter image description here

the formula you are looking for should be like this:

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

Upvotes: 1

Related Questions