2

I'm trying to use conditional formatting in the goals area. Currently the calorie goal (J10) turns green as long as I stay under its number, and it's compared to the total calories for the day (J3). The problem is that when I copy everything down to a new day in the bottom, everything moves in a proper relative manner EXCEPT the formatting reference. It still refers to J3 instead of J16.

My Current Sheet

How do I fix this without having to make a new format for every new day of goals?

Improve this question
3
  • Related: Relative copy conditional formatting that references other worksheet, which is a different solution.
    – user79865
    Commented Jun 18, 2015 at 23:18
  • Wow. That's very strange that there's no way that it actively updates the display of the reference. But thank you, that helped me figure it out. I appreciate it.
    – Tim
    Commented Jun 18, 2015 at 23:26
  • Okay, I'll post an answer then.
    – user79865
    Commented Jun 18, 2015 at 23:32

1 Answer 1

Reset to default
1

In order for conditional formatting references to be remapped in the way you want, you should copy-paste a range of cell including the one being formatted and the one being referenced. In your case, with custom formula =J10<=J3, you can copy the range J3:J10 down to J16:J23, and as a result, the formatting in J23 will apply if the value in J23 is less than or equal the value in J16.

Note that the appearance of the custom formula does not change: you will see =J10<=J3 being applied to both J10,J23, with no explicit indication of remapping.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.