[Solved] Conditional formatting cell references not updated

[NCJECulver]

Is this a bug?

When copying and pasting cell content Calc updates cell references in the formuli appropriately. It does not, however, update cell references in conditional formatting. Example:

I have a spreadsheet for calculating student grades. Column one is student names. The rest of the row is conditionally formatted such that if the first column is blank (no student) the rest of the row is hidden. My conditional formatting statement is this:

Formula is IF($A$1="") Cell Style is Hidden.

If I copy and paste A2 to B2, the conditional formatting of B2 still references A1 instead of being updated to B1, forcing me to manually update conditional formatting every time I copy and paste. Very time consuming.

Is this a bug or per design? Is there a solution?

CJ

[Hagar Delest]

Change the formula to IF(A1="").

See here for more information: [Tutorial] Absolute, relative and mixed references.

Thanks to add '[Solved]' at beginning of your first post title (edit button) if your issue has been fixed.

[NCJECulver]

Simple as that, huh? Thanks -- looks like it did the trick. And thanks for the link to the tutorial. Lots of stuff I didn't know.

[Villeroy]

Simple as that, huh? Thanks -- looks like it did the trick. And thanks for the link to the tutorial. Lots of stuff I didn't know.

Yep, the references used in conditional formatting refer relatively to the currently active cell. Imagine you would type the formula into the active cell. Additionally selected cells behave like copies, expanding relative references from the active cell like in copied formulas.
However, there is a known bug which does not update references in conditional formatting: When you move a referenced cell/range, the movement will not be reflected in conditional formatting. Same issue with cell validation. A conditional format "Cell value =A1" will not update to X1 after moving A1 to X1 (regardless of relative or absolute). So don't apply validation and conditional formatting before your table layout is finished.