Excel 2007 : Visual representation of rota

For week1 MONDAY I've put in some conditional formatting for the boxes you want to "color" themselves. You can copy these ranges enmasse to the other sections once you see how it's done. Once copied, you will have to update the "watch" part of the CF formulas from column C to columns AK, BS, etc.

You have created a maintenance bear by using a different "color" for each row. If you used a single color for the banding, you could put one CF formula in the first cell for each day grid and then copy it to the entire day table. As it is, you will have to highlight and edit each row individually.

Originally Posted by JBeaucaire

For week1 MONDAY I've put in some conditional formatting for the boxes you want to "color" themselves. You can copy these ranges enmasse to the other sections once you see how it's done. Once copied, you will have to update the "watch" part of the CF formulas from column C to columns AK, BS, etc.

You have created a maintenance bear by using a different "color" for each row. If you used a single color for the banding, you could put one CF formula in the first cell for each day grid and then copy it to the entire day table. As it is, you will have to highlight and edit each row individually.

Hi JBeaucaire. Thanks for looking.

I can see the effect of the formatting but not the formatting itself. Where can I view this?

Thanks, Phil.

update: I've found the formatting, just tinkering to see how I copy it to other cells...

1) I merged one set of cells in the first column for the first employee. Then I set the CF formula for that row. Then I copied the cell ACROSS for that employee and it should merge the following columns appropriately for that employee.

If you opted to use one color for all the bars, you could merge the one set, set the CF formula and then copy it across the employee and down for the other employees in that same section. Your call.

2) With the desire to have the boxes have summable data, you move the CF formula into the cells themselves to provide a simple summable value like "1".

The CF formula then becomes VERY simple...color the cell if the cell has a 1 in it.

I've done that on this sheet for week1 for you.

I also added the formula for summing the segments across row 28.

I also added formulas in column B to sum the hours for the week for you, also easy to do now that there is a value in each cell that can be summed.