Conditional formatting over several cells

[CJA007]

Hi, I have a large holiday spreadsheet for my staff. The dates are in columns and their names are in the rows. I want to highlight if two or more people from the same work area are on holiday at the same time. Ideally this will flag when their holiday is added and warn them that they may not be able to take that specific time.

Many thanks to everyone.

CJA

 

[Special-K99]

When you say the dates are in columns, how many columns?
One column with from and to in or are there two columns that contain one date each the first being from the second being to?
Or some other date layout?

You havent said where the work area is, you mentioned dates and name but no work area.
Based on the above you're better off posting an image of what the data looks like, produce a mock up if you feel its sensitive.

 

[Cross Terrier]

[TABLE="width: 500, align: left"]
<tbody>[TR]
[TD]Name[/TD]
[TD="align: center"]Jan 1[/TD]
[TD="align: center"]Jan 2[/TD]
[TD="align: center"]Jan 3[/TD]
[TD="align: center"]Jan 4[/TD]
[TD="align: center"]Jan 5[/TD]
[TD="align: center"]Jan 6[/TD]
[TD="align: center"]Jan 7[/TD]
[TD="align: center"]Jan 8[/TD]
[TD="align: center"]Jan 9[/TD]
[/TR]
[TR]
[TD]Adrian[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD]Alessia[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD]Alex[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]1[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]1[/TD]
[/TR]
[TR]
[TD]Alison[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]1[/TD]
[TD="align: center"][/TD]
[TD="align: center"]1[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD]Andrew[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD]Anthony[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]1[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD]Total[/TD]
[TD="align: center"]6[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[/TR]
</tbody>[/TABLE]














This is a really basic method, but it works. I made the formatting in my spreadsheet with the entire cells filled, rather than just the font coloured, and that looks better and allows me to have the columns with a total of 0 in bright green.

The formatting is simply based on the total: if it's greater than 2 the cell is red, greater than 1 orange, then light green and bright green for 1 person on holiday and none. You can change the values here depending on how many staff you have in each department and how many are allowed to be off at once.

Just select cell B2 and enter the formatting rule as B$9>2 with the format red, then a new rule that goes second in the order as B$9>1, and so on so that they are in the correct order. Then copy and paste the format all across the year and the staff cells and it will appear in the colour depending on how many people have holidays booked then.

Drawbacks to this method: maybe the staff members don't want their holidays to be shown to the entire department in advance (the same guy keeps stopping you from booking the days you wanted but you got in second?); there's no way of showing a public holiday on which the entire workforce might expect to be off.