Bug Hunting Session
Bug 48970 - EDITING Conditional Formatting: Absolute references not fitted
Summary: EDITING Conditional Formatting: Absolute references not fitted
Status: RESOLVED FIXED
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
Inherited From OOo
Hardware: Other All
: medium normal
Assignee: Markus Mohrhard
URL:
Whiteboard: target:4.0.0
Keywords:
: 55941 (view as bug list)
Depends on:
Blocks:
 
Reported: 2012-04-20 07:21 UTC by Jiri Kosina
Modified: 2012-11-28 06:28 UTC (History)
8 users (show)

See Also:
Crash report or crash signature:


Attachments
XLS file demonstrating the problem (91.00 KB, application/vnd.ms-excel)
2012-04-20 07:21 UTC, Jiri Kosina
Details
Screenshot before deletion (156.89 KB, image/jpeg)
2012-04-20 07:23 UTC, Jiri Kosina
Details
Screenshot after deletion (150.41 KB, image/jpeg)
2012-04-20 07:24 UTC, Jiri Kosina
Details
another 'broken' spreadsheet (13.69 KB, application/vnd.oasis.opendocument.spreadsheet)
2012-09-17 20:53 UTC, Olav Seyfarth
Details
Simple Sample (11.41 KB, application/x-vnd.oasis.opendocument.spreadsheet)
2012-11-26 06:51 UTC, Rainer Bielefeld Retired
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Jiri Kosina 2012-04-20 07:21:41 UTC
Created attachment 60391 [details]
XLS file demonstrating the problem

Steps to reproduce:

1. open the attached XLS file
2. Switch to "Prezentace" sheet
3. Delete column C
4. See the text that was in for example Column D before deletion (i.e. Column C after deletion) vanish.

It's still showing in the editation bar when the cell is selected, but it's not disaplyed in the document any more.
Comment 1 Jiri Kosina 2012-04-20 07:23:39 UTC
Created attachment 60392 [details]
Screenshot before deletion
Comment 2 Jiri Kosina 2012-04-20 07:24:03 UTC
Created attachment 60393 [details]
Screenshot after deletion
Comment 3 Rainer Bielefeld Retired 2012-04-20 13:45:20 UTC
I see the effect with "LibreOffice 3.5.3.1 German UI/Locale [Build-ID: 21cb047-d7e6025-9ba54fc-b4a51a8-f42372b] on German WIN7 Home Premium (64bit), cell contents in cells below "Jméno a příjmení" no longer visible after deletion of one of the columns A ... C.

But may be that is more or less intended? There is a conditional formatting "Excel_CondFormat_1_2_1" for cells in column D what makes cell contents invisible, and may be after column deletion condition for that formatting are fulfilled? Currently I do not see a bug.

@Jiri Kosina:
it would be great if you could help at the tedious work to find out what's happening there.
Comment 4 Jiri Kosina 2012-04-21 04:05:53 UTC
(In reply to comment #3)
> I see the effect with "LibreOffice 3.5.3.1 German UI/Locale [Build-ID:
> 21cb047-d7e6025-9ba54fc-b4a51a8-f42372b] on German WIN7 Home Premium (64bit),
> cell contents in cells below "Jméno a příjmení" no longer visible after
> deletion of one of the columns A ... C.
> 
> But may be that is more or less intended? There is a conditional formatting
> "Excel_CondFormat_1_2_1" for cells in column D what makes cell contents
> invisible, and may be after column deletion condition for that formatting are
> fulfilled? Currently I do not see a bug.

I doubt that this is intended, as in Microsoft Office the contents of the cells is still visible after the same deletion operation.
Comment 5 Rainer Bielefeld Retired 2012-04-21 04:09:34 UTC
I will do some further research with MS Office later
Comment 6 Rainer Bielefeld Retired 2012-04-24 04:37:14 UTC
I still think it's a conditional formatting problem. I see that the contents does not become invisible with EXCEL Starter 2010. Reference for conditional formattings in column D behaves different in LibO and EXCEL.
EXCEL: $D17="" becomes $C17 after deletion of column C
LibO:  Prezentace.$D17="" remains $D17

Currently no spare time for more research.
Comment 7 bfoman (inactive) 2012-06-22 05:19:36 UTC
Confirmed with:
LO 3.5.4.2 
Build ID: own W7 debug build
Windows 7 Professional SP1 64 bit

(In reply to comment #0)
> It's still showing in the editation bar when the cell is selected, but it's not
> disaplyed in the document any more.

After selecting the cells and using Clear Direct Formatting the content is visible.
Comment 8 Valek Filippov 2012-07-11 00:37:27 UTC
Another interesting thing is ^Z after deleting column.
In 3.6b2 it screws-up things even more.
Comment 9 Olav Seyfarth 2012-09-17 20:53:40 UTC
Created attachment 67295 [details]
another 'broken' spreadsheet

I encounter the same problem on 3.6.1.2 (german) with this spreadsheet. You can trigger the error by marking lines 16+17 and then cutting or deleting them.
Comment 10 Joel Madero 2012-11-21 22:12:30 UTC
Rainer: I tend to agree that this isn't intended, or if it is, an enhancement may be in order. What do you think?
Comment 11 Rainer Bielefeld Retired 2012-11-22 06:20:10 UTC
@Joel
I completely agree with your doubts that that behavior is useful (although might be intended).  But I still still am far away from understanding this problem. May be "Bug 57395 - FORMATTING: modifying reference for CONDITIONAL FORMATTING of a single cell also modifies references in adjacent cells" can help to understand the effects here?
Comment 12 Rainer Bielefeld Retired 2012-11-22 06:39:59 UTC
@Olav
I see absolutely nothing. Please describe exactly what problem you see in your sample document

@bfoman
Have you any idea what is happening here? 

---

I created a more simple sample document with following important modifications:
a) I modified Style "Excel_CondFormat_1_2_1", added green background (so that it can be observed more easy, test it by deleting contents of D17), 
b) added a  formula to D26, referring to $E$26

Can someone explain my following observation with  "LibreOffice 3.5.7.2 rc German UI/Locale [Build-ID: 3215f89-f603614-ab984f2-7348103-1225a5b] on German WIN7 Home Premium (64bit) 

0. Open Attachment 70408 [details] (simpyfied version of att. 2012-04-20 07:21 UTC, 
   Jiri Kosina)
1. Check conditional formatting in D17
   > is 'Sample.$D17=""'
2. Check refeerence in D26: is to '$E$26'
3. Delete column C
   > Range B11:C22 gets green background ?!?
4. Redo step 2
   As expected in (now) C26 the reference changed from '$E$26' to '$D$26'
5. Redo step 1 for (now) C17
   Unexpectedly (?) the reference for conditional formatting 
   did not change from 'Sample.$D17=""' to 'Sample.$C17=""'

The reason for that effect might be that the deletion of column C was within the range for conditional formatting, see Bug 57395. If that's the reason, the new question is where we find buggy behavior - in Excel or in LibO?

My skills here are too limited to find the core of the problem.
Comment 13 Joel Madero 2012-11-25 04:37:18 UTC
@Rainer -- same my skills definitely aren't in this area. Maybe worth while to CC Calc Expert?
Comment 14 Rainer Bielefeld Retired 2012-11-25 07:10:21 UTC
@Joel:
I doubt that this is xls related, I still am planning some additional research until next week
Comment 15 Rainer Bielefeld Retired 2012-11-26 06:47:21 UTC
I created a more simple document reduced to the relevant.
My results with parallel installation of  "LOdev  4.0.0.0.alpha1   -  ENGLISH UI / German Locale  [Build ID: dec8fe)]"  {tinderbox: @6, pull time 2012-11-13 06:07:28} on German WIN7 Home Premium (64bit) with separate /4 User Profile for Master Branch: Absolute references for conditions in formulas will not be fitted correctly after insert or delete column or row.

A) Tests with "sample.ods"
--------------------------
You can recapitulate the "normal behavior for absolute references in sheet "Sample1" in line 32. All Formulas in E32 ... I32 refer to the number "100" in cell J32. When you insert a column with cursor in column A the reference in E32 changes from "=$J$32" to "=$K$32/1", so that the target of the reference remains the "100" with blue borders.

Undo before next test!

B) Now check the behavior with references.
--------------------------------------------
D2:I4 contains Conditional Formatting referring to column K with an absolute column reference, if cell in column K is empty the background of cells with CF will be yellow, if K is not empty background disappears. 
Due to results of 'A' references like '$K2=""' should change to  '$L2=""' after you have inserted a Column with cursor in Column A. But When you check Cell E2 after column insertion you see that reference still is '$K2=""', and so the background disappeared, because letters "j" now are in column K

The effect is the same in cell D11, where the CF only is applied for a single cell. 

The effect also appears for column deletion, as you see in D5:E7 and D112

No problem with relative references as you see in D15 and D16

The effect also is visible for insertion or deletion of rows, as you see in Sheet2 with insertion or deletion of row 1

Has already been reported as "Bug 55941 - EDITING: Conditional Formatting, Insert/Delete Column/Row only update cell references in CF when there are not absolute references.", I do not know why reporter marked that one "invalid".

It seems that Excel does this job correctly. 

Same effect with LibO 3.3.3 an OOo 3.1.1, so this one seems to be inherited from OOo, and indeed, there is a very old OOo issue.

@Spreadsheet Team
Please set Status to ASSIGNED and add yourself to "Assigned To" if you accept this Bug or forward the Bug if it's not your turf (and remove others in team from CC).
Comment 16 Rainer Bielefeld Retired 2012-11-26 06:51:14 UTC
Created attachment 70567 [details]
Simple Sample

See comment before how to use this sample document

Obsoleted attachment 60391 [details] shows that Excel does the job correctly
Comment 17 Rainer Bielefeld Retired 2012-11-26 06:59:35 UTC
*** Bug 55941 has been marked as a duplicate of this bug. ***
Comment 18 Markus Mohrhard 2012-11-26 11:32:20 UTC
The conditional format design allows to fix this quite easily. Fixed now in master for 4.0
Comment 19 Not Assigned 2012-11-26 11:43:25 UTC
Markus Mohrhard committed a patch related to this issue.
It has been pushed to "master":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=a1ba48f312e9a5a1c8383775a0f0ef951832f4d4

we can now update formulas in cond formats correctly, fdo#48970



The patch should be included in the daily builds available at
http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
http://wiki.documentfoundation.org/Testing_Daily_Builds
Affected users are encouraged to test the fix and report feedback.
Comment 20 Rainer Bielefeld Retired 2012-11-26 11:45:26 UTC
Wow, thank you, that was some more than light speed :-)
Comment 21 Joel Madero 2012-11-28 06:08:33 UTC
amazing, thanks Markus
Comment 22 Olav Seyfarth 2012-11-28 06:28:43 UTC
(In reply to comment #12)
> @Olav
> I see absolutely nothing. Please describe exactly what problem you see in
> your sample document

What happened when I had 3.6.1.2 installed was that more or less the whole sheed got garbled, sorry I didn't take screenshots. My bug is no longer reproducable using LibreOffice Version 3.6.3.2 (Build ID: 58f22d5).