Results 1 to 10 of 10

Thread: Fill cells if other cells are filled

  1. #1

    Fill cells if other cells are filled



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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...

    Click image for larger version. 

Name:	Picture1.jpg 
Views:	15 
Size:	97.0 KB 
ID:	3177

    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

  2. #2
    Super Moderator Bob Phillips's Avatar
    Join Date
    Mar 2011
    Posts
    1,820
    Articles
    0
    Excel Version
    O365
    How does the current fill colour get applied.

    A workbook rather than a picture would help.

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

  4. #4
    Super Moderator Bob Phillips's Avatar
    Join Date
    Mar 2011
    Posts
    1,820
    Articles
    0
    Excel Version
    O365
    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.

  5. #5
    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?

  6. #6
    Super Moderator Bob Phillips's Avatar
    Join Date
    Mar 2011
    Posts
    1,820
    Articles
    0
    Excel Version
    O365
    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.

  7. #7
    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.

  8. #8
    Super Moderator Bob Phillips's Avatar
    Join Date
    Mar 2011
    Posts
    1,820
    Articles
    0
    Excel Version
    O365
    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
    Attached Files Attached Files

  9. #9
    That is brilliant Bob, working very nicely now.

    Thanks!

  10. #10
    Super Moderator Bob Phillips's Avatar
    Join Date
    Mar 2011
    Posts
    1,820
    Articles
    0
    Excel Version
    O365
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •