Conditional Formatting

[liampog]

Hi

I have the following data on a sheet. It is basically a calendar that shows a persons days off.

Role 0 are fixed days off people. Role 1 is to be ignored.

I want a conditional formatting to be true when the following are satisfied:

1. Column B = 0
2. If the day (Row 1) for the corresponding date is TRUE in Columns C to I.

I can't figure out what formula I need to put into the conditional formatting.

The columns extend beyond Column W up to the last day of the year (so 365/366 columns in total).

I've placed X in the cells that I want to colour (there won't actually be any data in the cells and the X's are just to clarify the cells which will be true in the conditional format).

I hope I've worded this clearly.

Thanks
Liam

[TABLE="class: grid, width: 500"]
<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]
[TD]H[/TD]
[TD]I[/TD]
[TD]J[/TD]
[TD]K[/TD]
[TD]L[/TD]
[TD]M[/TD]
[TD]N[/TD]
[TD]O[/TD]
[TD]P[/TD]
[TD]Q[/TD]
[TD]R[/TD]
[TD]S[/TD]
[TD]T[/TD]
[TD]U[/TD]
[TD]V[/TD]
[TD]W[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Name[/TD]
[TD]Role[/TD]
[TD]Mo[/TD]
[TD]Tu[/TD]
[TD]We[/TD]
[TD]Th[/TD]
[TD]Fr[/TD]
[TD]Sa[/TD]
[TD]Su[/TD]
[TD]Mo[/TD]
[TD]Tu[/TD]
[TD]We[/TD]
[TD]Th[/TD]
[TD]Fr[/TD]
[TD]Sa[/TD]
[TD]Su[/TD]
[TD]Mo[/TD]
[TD]Tu[/TD]
[TD]We[/TD]
[TD]Th[/TD]
[TD]Fr[/TD]
[TD]Sa[/TD]
[TD]Su[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]1/1[/TD]
[TD]2/1[/TD]
[TD]3/1[/TD]
[TD]4/1[/TD]
[TD]5/1[/TD]
[TD]6/1[/TD]
[TD]7/1[/TD]
[TD]8/1[/TD]
[TD]9/1[/TD]
[TD]10/1[/TD]
[TD]11/1[/TD]
[TD]12/1[/TD]
[TD]13/1[/TD]
[TD]14/1[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Joe Bloggs[/TD]
[TD]0[/TD]
[TD]TRUE[/TD]
[TD]TRUE[/TD]
[TD]TRUE[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]John Smith[/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Alan Jones[/TD]
[TD]0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]TRUE[/TD]
[TD]TRUE[/TD]
[TD]TRUE[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]John Doe[/TD]
[TD]0[/TD]
[TD]TRUE[/TD]
[TD][/TD]
[TD][/TD]
[TD]TRUE[/TD]
[TD][/TD]
[TD][/TD]
[TD]TRUE[/TD]
[TD]x[/TD]
[TD][/TD]
[TD][/TD]
[TD]x[/TD]
[TD][/TD]
[TD][/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD][/TD]
[TD][/TD]
[TD]x[/TD]
[TD][/TD]
[TD][/TD]
[TD]x[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]Mary King[/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]James May[/TD]
[TD]0[/TD]
[TD]TRUE[/TD]
[TD]TRUE[/TD]
[TD][/TD]
[TD][/TD]
[TD]TRUE[/TD]
[TD]TRUE[/TD]
[TD][/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD][/TD]
[TD][/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD][/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD][/TD]
[TD][/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[DanteAmor]

The formula is based on the date you have in cell J2, since the day in J1 is incorrect, it should be Tuesday.

Change 8 for the last line with data.

=IF($B3=0,1)*INDEX($A$1:$I$8,MATCH($A3,$A$1:$A$8,0),MATCH(LEFT(TEXT(J$2,"ddd"),2),$A$1:$I$1,0))

Change W for the last column with data.
Applies to:
$J$2:$W$8

 

[liampog]

Hi Dante

Thanks, that's brilliant, and actually the formula makes perfect sense to me just couldn't work it out for myself.

To be honest, I just made the days and dates up so was able to modify the formula to work correctly.

Thanks again
Liam

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.