Download it now!
Bug 51015 - EDITING: conditional formating does not update automatically
Summary: EDITING: conditional formating does not update automatically
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
(earliest affected)
3.5.4 release
Hardware: x86 (IA32) Windows (All)
: medium minor
Assignee: Not Assigned
Depends on:
Blocks: Conditional-Formatting
  Show dependency treegraph
Reported: 2012-06-12 12:40 UTC by reussandras
Modified: 2019-12-22 15:42 UTC (History)
6 users (show)

See Also:
Crash report or crash signature:

a sample to work on (7.93 KB, application/vnd.oasis.opendocument.spreadsheet)
2012-06-13 00:09 UTC, reussandras
Cells with conditional format in copied sheet are not refreshed automatically (6.68 KB, application/vnd.oasis.opendocument.spreadsheet)
2013-12-11 17:22 UTC, j.tronel

Note You need to log in before you can comment on or make changes to this bug.
Description reussandras 2012-06-12 12:40:48 UTC
It seems that I have to type f9 or change the magnification if I want to get updated the conditional formating cells. I think it should do it automatically.
Comment 1 Rainer Bielefeld Retired 2012-06-12 21:08:10 UTC
Not a valid bug report.

I can't remember to have seen something like that with "LibreOffice  German UI/Locale [Build-ID: 165a79a-7059095-e13bb37-fef39a4-9503d18] on German WIN7 Home Premium (64bit) 
Thank you for your report – unfortunately important information is missing.
May be hints on <> will help you to find out what information will be useful to reproduce your problem? If you believe that that  is really sophisticated please as for Help on a user mailing list
- Write a meaningful Summary describing exactly what the problem is
- Attach a sample document (not only screenshot) or refer to an existing 
  sample document in an other Bug with a link.
- Contribute a document related step by step instruction containing every 
  key press and every mouse click how to reproduce your problem 
  (similar to example in Bug 43431)
- add information 
   -- concerning your PC 
  -- concerning your OS (Version, Distribution, Language)
  -- concerning your LibO UI language, Locale setting
  –- Libo settings that might be related to your problems 
  -- how you launch LibO and how you opened the sample document
  -- everything else crossing your mind after you read linked texts

Even if you can not provide all demanded information, every little new information might bring the breakthrough.

May be you can test <> for submitting bug reports? You reach that Bug Submission Assistant via LibO menu 'Help -> Feedback / Bug Report'
Comment 2 reussandras 2012-06-13 00:09:22 UTC
Created attachment 62949 [details]
a sample to work on

In the attached document, I can change the value in G2, but the range formatting does not change, only on keypress F9 or something that needs a screen refresh.
Comment 3 QA Administrators 2013-05-26 22:31:57 UTC Comment hidden (obsolete)
Comment 4 ign_christian 2013-05-27 07:58:24 UTC
I can confirm reproducible on LO (Win7 32bit)

Changing cell G2 to 1000 isn't updating cells C2:D10 format. Changing applied after clicking save.
Comment 5 Jorendc 2013-05-27 19:15:23 UTC
(In reply to comment #4)
> I can confirm reproducible on LO (Win7 32bit)
> Changing cell G2 to 1000 isn't updating cells C2:D10 format. Changing
> applied after clicking save.

So we can mark it as NEW.
Comment 6 Jorendc 2013-05-27 19:15:58 UTC
CC'ing our conditional formatting expert :), Markus
Comment 7 j.tronel 2013-12-11 17:22:13 UTC
Created attachment 90616 [details]
Cells with conditional format in copied sheet are not refreshed automatically

Presumably a slightly different manifestation of this bug
(LO Windows 7 - 64)

In sheet Sh1, cells A1:A8 have a red background if A1 contains a negative value.
Conditional format works as expected.

Sh2 was created as a copy of Sh1 (right click on tab)
On modification of A1, the background of A2:A8 remains unchanged.

Some actions restoring proper display :
-- ctrl+shift+F9 (not F9 by itself)
-- moving or redimensioning the window
-- going to and from another sheet...
Comment 8 QA Administrators 2015-04-19 03:20:40 UTC Comment hidden (obsolete)
Comment 9 Buovjaga 2015-06-15 12:37:38 UTC
(In reply to ign_christian from comment #4)
> I can confirm reproducible on LO (Win7 32bit)
> Changing cell G2 to 1000 isn't updating cells C2:D10 format. Changing
> applied after clicking save.

Yep, needs ctrl-shift-f9.

Lowering severity

Win 7 Pro 64-bit Version:
Build ID: 01a189abcd9a4ca472a74b3b2c000c9338fc2c91
TinderBox: Win-x86@39, Branch:master, Time: 2015-06-14_07:46:28
Locale: fi-FI (fi_FI)
Comment 10 QA Administrators 2016-09-20 10:29:05 UTC Comment hidden (obsolete)
Comment 11 Toby 2017-03-14 08:28:26 UTC
I have this problem in LO

If I load the 'Cells with conditional format in copied sheet.." file in the attachments, and change A1 from -5 to 5, the red cells should go white, but they don't until I press CTRL-SHIFT-F9.

This was a problem for me in my own spreadsheet, until I went to put in a bug report and found out about CTRL-SHIFT-F9 - I didn't even know about this keyboard command, and still don't know how it differs from F9, which should calculate!

I have 'Autocalculate' switched on BTW, so I expected it to re-calculate.
Comment 12 QA Administrators 2018-06-24 02:41:56 UTC Comment hidden (obsolete)
Comment 13 reussandras 2018-06-24 11:30:22 UTC
It work fine on debian jessie system (the default package)

It does not work correctly, but the way it described in this issue.
Comment 14 b. 2019-03-11 06:41:32 UTC
hello @all ... to whom it may concern ... , 
sorry for undigging an old thread, i'm on the hunt for a bug, 
not calculating the conditional format for C2:D10 in sample 1 while: 
a) - being able to do so, strg-shift-F9 produces results as expected, and
b) - doing it with manually triggered forced recalc (strg-shift-F9), 
(both tested with ver., win7-x64) 
shows two things to me, 
1) - despite being complex the formula for the conditional formatting is ok, 
2) - something for the view / display / calculation of the cells is excluded from autocalculate (normally it shouldn't be like that), 
3) - already in this version it is possible that aspects of cells become excluded from autocalculate (that's what i'm hunting for), 
additional results: 
A) - it's not dependent on pointing to ranges, 
B) - it's somehow dependent on pointing to a cell in the same row, works better on them, 
C) - other influences, in sample 2 switching the sheet, touching conditional formatting and similar have the same effect as forced recalc, (was mentioned in former comments), 
D) - in sample 1 it's somehow dependent on the complexity of the formula and pointing to ranges, a workaround with two conditions with simpler formulas (less "$", no absolute sheet reference) pointing to ranges in single columns works as expected, 
E) - sample 1 works as expected with ver. from 2019-03-09, it's either fixed or plastered by changes in 4.2 to 6.3, 
F) - E) is not the case for sample 2, it works as buggy in 6.3 as it has been in 4.1,

who / what the heck made anything in the displayed result of a sheet dependent on forced recalc (recalculations not done without additional triggering),
Comment 15 b. 2019-03-24 11:44:38 UTC
in sample 2 with ver 

A.) the conditional format is! applied to cells A2 to A8 in the copied sheet after! input to / edit of one of these cells, not to all of them, but to all below the changed cell, 

B.) new copies of sh1 work as buggy as sh2, 

with ver

C.) effect A.) changes from affecting cells below the edit to affecting all cells in the 'group' A2:A8

D.) new copies of sh1 work correctly, while sh2 still fails, 

funny side effects: 

1.) a 'non changing edit' like input of a space and deleting it before pressing enter or just touching the cell with 'F2 and enter' affects the touched cell, but not! the others in the 'group' to get the intended formatting, while input of a value affects the other cells in A2:A8 as well (not visible if it's affecting A1 as this is changed beforehand), 

2.) effect 1.) changed from affecting the whole group to only the edited cell on real edits after changing the formula for the conditional format from '<0' to '>0' and stayed that way when changing back to the old formula, 

this is the first / oldest occurence of: 

'autocalculate not working correctly for groups of cells'

i've found yet, not sure how much it is 'related' to other bugs like #123714, #123736, #124270, but i assume it may be ... it's something in the area of 'having a group', and keeping most of it's definition but breaking it apart for the 'recursion?' of autocalculate on 'actions' with or inside the the group of cells or referenced ranges ... 

just my two cents ... 

suggestion: estimated from effect D.) there must be something stored as 'property' in the file that's 'related' to whether cells are autocalculated correctly. as it is possible to have 'old' sheets with 'broken' content, formatting or functionality in it, imho it would be a great idea to add a 'file checking utility' to calc which - either triggered manually or like the recalculation of excel files or 'not saved with lo' files on load of a file - could run and check for occurences of 'old misbehaviour' or 'problems with files saved by buggy versions', and either warn the user about it or correct the fault (!with notice to the user that his file will change it's behaviour!), 

- i've read something about the usances in this community and that it's wrong to write what follows, but feel under pressure to bring it in discussion. 

- this error is around for nearly 7 years now, and not yet fundamentally fixed. it might be 'related' to plenty other bugs (i've heard something about 'code re-use' in modern styles of programming), thus it might be difficult to get out the other errors while this one is unresolved. i'd like to change the importance to 'critical', but will let that to other users who know better if and when steps like this are suitable. 

- you / we / the community advertise(s) with 'the spreadsheet you ever wanted' or similar ... imho fundamental bugs in calculating (also if they 'only' affect presentation of results, such things may be very important for the user and the use and usability of the results) should be handled 'in time' and in a proper way. the codebase has to be clear and reliable for all things affecting calculations and results before! we argue about the size of triangels in symbols and spend time for things like that ... 


Comment 16 b. 2019-03-25 09:26:42 UTC
hint to boil down the problem, 

the 'irregularity' stored in sh2 in the sample file (2) affects that the conditional formatting of A1:A8 on sheet 2 is 'not copyable', neither by copying the sheet into another sheet, nor by copying the range A1:A8 to another sheet, nor inside the same sheet, despite the fact that the formatting lokks 'normal' when you access editing of conditional formats, and is correctly defined for the range of cells. 

the conditional format defined on Sh1 is copyable in all mentioned ways.  

if te conditional formatting on Sh2 is defined new - delete and create new - it works as it should.
Comment 17 b. 2019-12-22 01:58:31 UTC
format description in'content.xml' for sh2 in second sample file is: 

<calcext:conditional-formats><calcext:conditional-format calcext:target-range-address="Sh1.A1:Sh1.A8"><calcext:condition calcext:apply-style-name="Rouge" calcext:value="formula-is([.A$1]<0)" calcext:base-cell-address="Sh1.A1"/></calcext:conditional-format></calcext:conditional-formats>

that's referencing sh1 instead of sh2 and thus not what was intended ... 

i can't explain how this came there ... but doubt anybody else can ... 

i think we can close this as 'buggy file', 

tested with ver: 

Version: (x64)
Build ID: 209fc9fd7fa433947af0bf86e210d73fa7f5a045
CPU threads: 8; OS: Windows 6.1 Service Pack 1 Build 7601; UI render: default; VCL: win; 
Locale: de-DE (de_DE); UI-Language: en-US
Calc: CL
Comment 18 Buovjaga 2019-12-22 09:50:57 UTC
b.: thanks for digging. Indeed the original sample now works fine. Let's close as WFM, then, and ignore the second issue as it was invalid.
Comment 19 reussandras 2019-12-22 15:42:37 UTC
It works as expected in 
Build ID: 1:6.1.5-3+deb10u5
CPU threads: 8; OS: Linux 4.19; UI render: GL; VCL: gtk3_kde5; 
Locale: hu-HU (hu_HU.UTF-8); Calc: group threaded