This is a reporting presentation exercise...

I am looking for the magic to change the colour of the cell without changing the contents of the cell.

I have a type of crosstab table, which has the ROWS individual CustomerIDs, and the COLs as months.

PRESENTATION TABLE

And in a separate table, I have some data which I want to use in a conditional formatting type of way.

LOOKUP TABLE

Let's say that if the CustomerID and Period in the data table has the label "Applied" then I want that cell in the top table, where CustomerID and Period intersect, to be coloured RED. If it's "Passed", I want it to be coloured green, and so on.

The periods are dates data types. The tables are excel tables.

The dollar value in the cell has nothing to do with it.

I've seen some techniques where a combination of Named ranges and confditional formatting are used, but I'm stumped.

I can put the dollar values in the correct cell by putting a helper column into my source data (the second table) and then doing an xlookup on the combination of the row and col headers, against that helper column.

But then I run out of steam.

END RESULT

CodePudding user response：

Conditional Formatting On Two Criteria In A Separate Table

This is what I have tried,

• Formula used in Conditional Formatting for Applied

=COUNTIFS($F$2:$F$5,$A2,$G$2:$G$5,B$1,$H$2:$H$5,"Applied")=1

Special credit to Scott Craner Sir for the above formula

• And for the Pending you may try using SUMPRODUCT() or COUNTIFS() Function as well, both way it works,

=SUMPRODUCT(($A2=$F$2:$F$5)*(B$1=$G$2:$G$5)*($H$2:$H$5="Pending"))=1