[Solved] Conditional formatting cell references not updated

Discuss the spreadsheet application

[Solved] Conditional formatting cell references not updated

Postby NCJECulver » Tue Jun 24, 2008 11:17 am

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
Last edited by NCJECulver on Tue Jun 24, 2008 11:36 am, edited 1 time in total.
NCJECulver
 
Posts: 18
Joined: Tue Mar 04, 2008 7:17 am

Re: Cond'l formatting cell references not updated

Postby Hagar Delest » Tue Jun 24, 2008 11:28 am

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.
LibreOffice 6.4.6 on Xubuntu 20.04 and Windows 10.
User avatar
Hagar Delest
Moderator
 
Posts: 29084
Joined: Sun Oct 07, 2007 9:07 pm
Location: France

Re: Cond'l formatting cell references not updated

Postby NCJECulver » Tue Jun 24, 2008 11:36 am

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.
NCJECulver
 
Posts: 18
Joined: Tue Mar 04, 2008 7:17 am

Re: Cond'l formatting cell references not updated

Postby Villeroy » Tue Jun 24, 2008 12:53 pm

NCJECulver wrote: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.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.4
User avatar
Villeroy
Volunteer
 
Posts: 28851
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany


Return to Calc

Who is online

Users browsing this forum: No registered users and 29 guests