Forum Discussion
Conditional Formatting--Relative References Not Working Correctly
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.
- SergeiBaklanNov 16, 2017Diamond 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 MortonNov 17, 2017Copper 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.