Gantt Chart with Conditional Formatting based on two conditions

[prvince]

Hi there,

I need help with a formula to apply conditional formatting based on two conditions. The data I am referring to is shown in the table below. The formula for the conditional formatting should format a cell based on the classic green, yellow, red formats when the row and column dates match and the status is posted (green), not yet posted (yellow) and not posted (red). I've put in brackets what the outcomes should be in the table below.

[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Name & Product[/TD]
[TD]Publish Date[/TD]
[TD]Status[/TD]
[TD]16-Aug-16[/TD]
[TD]23-Aug-16[/TD]
[TD]30-Aug-16[/TD]
[TD]06-Sep-16[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Joe Bloggs[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]1. I Am Bloggs T-shirt[/TD]
[TD]16-Aug-16[/TD]
[TD]Posted[/TD]
[TD][GREEN][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]2. Bloggs Denim Jacket[/TD]
[TD]23-Aug-16[/TD]
[TD]Posted[/TD]
[TD][/TD]
[TD][GREEN][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]3. I love Bloggs Underwear[/TD]
[TD]30-Aug-16[/TD]
[TD]Not Posted[/TD]
[TD][/TD]
[TD][/TD]
[TD][RED][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]4. Bloggs 4ever socks[/TD]
[TD]06-Sep-16[/TD]
[TD]Not Yet Posted[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][YELLOW][/TD]
[/TR]
</tbody>[/TABLE]





















The formula I'm using in D3 (and copied and pasted to the other cells) is as follows but no formatting appears in the cell or other cells.

="AND(D$1=$B3,$C3=""Posted"")" [GREEN]
="AND(D$1=$B3,$C3=""Not Yet Posted"")" [YELLOW]
="AND(D$1=$B3,$C3=""Not Posted"")" [RED]

Can anyone help please? Many thanks in advance.

P.S. Not sure if using Excel 2011 for Mac makes a difference...

 

[Constantinos]

Hello there,

In conditional formatting you can select new rule and apply these three rules for every cell
=IF(SUMPRODUCT((B3=$D$1:$G$1)*(C3="Posted"))=1, "True") and set the format to green
=IF(SUMPRODUCT((B3=$D$1:$G$1)*(C3="Not Posted"))=1, "True") and set format to red
=IF(SUMPRODUCT((B3=$D$1:$G$1)*(C3="Not Yet Posted"))=1, "True") and set format to yellow

hope it helps

 

[prvince]

Hello there,

In conditional formatting you can select new rule and apply these three rules for every cell
=IF(SUMPRODUCT((B3=$D$1:$G$1)*(C3="Posted"))=1, "True") and set the format to green
=IF(SUMPRODUCT((B3=$D$1:$G$1)*(C3="Not Posted"))=1, "True") and set format to red
=IF(SUMPRODUCT((B3=$D$1:$G$1)*(C3="Not Yet Posted"))=1, "True") and set format to yellow

hope it helps

Thanks Constantinos! It's such a clever formula and I definitely wouldn't think of something like this. I made a tweak to it because the current formulae would fill in all the cells because the $D$1:$G$1 would be true in all cases. This is what I used in the end:

=IF(SUMPRODUCT(($B3=D$1)*($C3="Posted"))=1, "True") and set the format to green
=IF(SUMPRODUCT(($B3=D$1)*($C3="Not Posted"))=1, "True") and set format to red
=IF(SUMPRODUCT(($B3=D$1)*($C3="Not Yet Posted"))=1, "True") and set format to yellow

This allows me to copy and paste, or drag, the formula to adjacent cells and the cell references would automatically update.

Anyway, I really appreciate the quick response and help! Thank you

 

[Constantinos]

Happy to help prvine