Forum Discussion
Conditional Formatting Formula Changes Erratically
Forgot to say, it's when changing the range we move reference in the formula out of the sheet. If modify my pervious example, start the rule from cell A17 with formula =A11>2 and after that apply the range to =A11:A15 when reference in the formula will be changed on =A5>2, i.e. on 6 rows up.
can u share the file, i can check further
bhushan_z File is attached, but the issue is more the process than the end result, so to duplicate it you would need to follow the steps.
Forgot to say, it's when changing the range we move reference in the formula out of the sheet. If modify my pervious example, start the rule from cell A17 with formula =A11>2 and after that apply the range to =A11:A15 when reference in the formula will be changed on =A5>2, i.e. on 6 rows up.
- Thanks for the explanation.
I understand that it is reproducible, and I understand that changing the reference causes the cell in the formula to move off the sheet.
I disagree that this is "expected behavior". I would have expected the formula I entered to stay the same, or at least some sort of warning that is has been changed automatically. 🙂
I have another question about conditional formatting, but I will post it separately for clarity.
Thanks!Formula can't be the same if you change ApplyTo range. That's the nature of conditional formatting, it iterates the formula within the range offsetting relative references in it to the "first" (top left) cell of the range. If we change the range manually it applies exactly the same offsetting. Same do Format Painter, and the same approach is for named formulas.
Formula will be kept the same only in the part where absolute references are used.
If you move the reference in the formula out of the sheet area we could expect some kind of error returned. Within conditional formatting we don't see what exactly formula returns (#REF in this case), thus it's end of the sheet range here.