Fill cells if other cells are filled

Hi guys, hoping someone might be able to help out.

I am currently creating a calendar for my departments events for the year, these are broken up into categories which can then expanded to see the individual items, which are marked by the corresponding cell being filled with a colour, as shown below...



What I would like to be able to do is offer an 'overview' for each section when they are collapsed. So in the above screenshot I would want H40 to O40 to be filled with a colour because H41, I42 - N42 and O43 are filled. I want to be able to do this using a formula so if events are changed it will automatically update the overview to reflect those changes.

Is this possible and can one of you wonderful people explain how to do it.

Thanks

How does the current fill colour get applied.

A workbook rather than a picture would help.

I just change the cell colour using the 'fill colour' tool.

Then I think you have a hard task ahead. The only way I can think of doing it automatically is applying conditional formatting, or event code, neither of which will pick up that you have coloured a cell using the fill tool. It needs to be data driven.

So if I were to add data to each of the calendar blocks, for example just give each one a period or comma, then would it be possible then?

I don't quite understand how the colours are allocated, but I would use different values (say x,y, z and so on) for each colour block, and either have conditional formatting or event code that reacts to that value, colours that cell, and also colours the top-level cell.

The colours simply refer to the quarter in which the event finishes, so blue for winter, green for spring, yellow for summer and orange for autumn.

The row I want auto filled will be a different colour again so all the events can have the same value. I only need the top row to be coloured automatically - the others we would do manually when we add/remove/change events.

This is the sort of thing I mean. Just put a letter w (Winter), s (Spring), x (Summer), or a (Autumn) in one of the appropriate slots and see what happens

That is brilliant Bob, working very nicely now.

Thanks!

Just in case it is not obvious, when you create a new block, copy one of the previous blocks (all rows), and past the formats onto the new one.