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.
Created attachment 60392 [details] Screenshot before deletion
Created attachment 60393 [details] Screenshot after deletion
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.
(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.
I will do some further research with MS Office later
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.
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.
Another interesting thing is ^Z after deleting column. In 3.6b2 it screws-up things even more.
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.
Rainer: I tend to agree that this isn't intended, or if it is, an enhancement may be in order. What do you think?
@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?
@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.
@Rainer -- same my skills definitely aren't in this area. Maybe worth while to CC Calc Expert?
@Joel: I doubt that this is xls related, I still am planning some additional research until next week
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).
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
*** Bug 55941 has been marked as a duplicate of this bug. ***
The conditional format design allows to fix this quite easily. Fixed now in master for 4.0
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.
Wow, thank you, that was some more than light speed :-)
amazing, thanks Markus
(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).