Forum Discussion

David Morton's avatar
David Morton
Copper Contributor
Nov 16, 2017

Conditional Formatting--Relative References Not Working Correctly

I am trying to set up a relatively simple calendar that automatically updates for changes in our company being opened or closed and uses another planning worksheet to update changes.

 

Anyway, I am trying to use conditional formatting to highlight those days that are not in the current month differently.  I am also trying to use conditional formatting to highlight those days that we are closed in another different way.  Each calendar day is represented by four different cells one cell has the day number in it.  The cell beside the day has the notice if we are closed, and the two cells that remain are merged in the row below for any special notations.

 

I am finding that if I do not use absolute cell references in my conditional formulas the formatting does not work correctly.  I would like to copy these multiple formulas over 35 different four-cell combinations, but to make the various formulas work I am having to go into Manage Conditional Formatting and open each individual rule and change the absolute references for each day grouping of cells.  I need an easier way to copy the formatting.  I am still only working on the first month.

 

Here are the way some of my rules are written:

1)  =$G$11<DAY($AD$2)                     where AD2 is the date of the opening of the season;  rule applies to G11:H12  (H11 & H12 are merged)

 

2)  =AND($G$11<15,$H$11="CLOSED")             this applies to G11:H12   the manager is also checked to stop if this is true

 

3)  =$H$11="CLOSED"                      this applies to G11:H12

 

4)  =$G$11<15                                 applies to G11:H12

 

 

I would really like all the cell references for cells G11 and H11 above to be able to be relative, but when I change them in the rule to relative only part of the 4 cells that I want to follow the rule actually are formatting correctly.  Some of the cells act as though there is no conditional formatting at all.  This has been especially noticeable in the cell H11.

 

I would like to be able to copy the conditional formatting easily to all of the days, but I cannot do that when only the absolute references are working.

 

I hope that you have some suggestions out there.

 

3 Replies

  • David Morton's avatar
    David Morton
    Copper Contributor

    Here is some more information about the problem that is described in my original post with an image to show the formatting issues.

     

     

    Here is the rule that creates the formatting for cells G13:H14 below: 

     

     

     

     

     

     

     

     

     

     

     

     

     

    And, here is the rule that uses some relative references for cells I13:J14: 

     

     

     

     

     

     

     

     

     

     

     

     

     

     

    I cannot figure out why the rule that uses the absolute references works perfectly fine, but the rule that was created for the range I13:J14 does not work correctly.  If you look below, you can see the actual results of the two rules, which are pretty much the same except that one has two dollar signs removed.

     

    In this image G13:H14 are formatted correctly, but they have only absolute references in their rules. Cells I13:J14 are only partially correct, and these rules are part absolute and part relative. The rules that are active are below.

     

     

     

    • SergeiBaklan's avatar
      SergeiBaklan
      Diamond Contributor

      Hi David,

       

      With relative references you shall includes all combinations in your rule. Perhaps could be simplified, but looks like

      =OR(OR(AND(I13<15,J13="CLOSED"),AND(I12<15,J12="CLOSED")),OR(AND(H13<15,I13="CLOSED"),AND(H12<15,I12="CLOSED")))

      With your rule if it check cell J13 it highlights the cell if J13 <15 and K13= CLOSED, etc.

      • David Morton's avatar
        David Morton
        Copper Contributor

        Thank you, Sergei.

         

        I think I understand what needs to happen.  It took me a few minutes to wrap my head around how the references work with your explanation.

         

        I will see how it goes.

        Each of my cells will have multiple conditions.

         

        Thanks again.

Resources