Issue 122856

Summary: Conditional formatting references wrong cells
Product: Calc Reporter: Pat <rockstar.pat>
Component: formattingAssignee: AOO issues mailing list <issues>
Status: CONFIRMED --- QA Contact:
Severity: Normal    
Priority: P3 CC: awf.aoo, clarence.guo.bj, issues, oliver.brinzing, orw
Version: 4.0.0Keywords: regression
Target Milestone: ---   
Hardware: PC   
OS: Windows 7   
Issue Type: DEFECT Latest Confirmation in: ---
Developer Difficulty: ---
Attachments:
Description Flags
Test file
none
Screen shot: 3.4.1 vs. 4.0.0
none
Comparison between MS Excel 2003 and 2010
none
simplified sample file
none
another simplified sample containing references to other sheets in the conditional formatting criteria none

Description Pat 2013-07-26 19:14:16 UTC
Created attachment 81168 [details]
Test file

After upgrading to 4.0.0, and opened a file I'd been working in previous versions it appears that my conditional formatting broken. All the rules were there, but they were all referencing the wrong cells. I went in and changed everything back to the way it should have been, saved the file and closed. When I opened the file back up again, everything was back referencing the wrong cells again. No matter how many times I update and save, it always goes back to being wrecked. 

I reinstalled and opened the file in 3.4.1, and everything was referencing the correct cells. 

Here are a couple examples of the same cells in 3.4.1 (how they should be) and 4.0.0 (wrecked) 

3.4.1 - Cell A1997 
COUNTIF($Sheet2.$A$1:$A$75;$Sheet1.A1997)>0 
IF(VLOOKUP($Sheet1.A1997;$Sheet2.$H$2:$I$44;2;0)<>$Sheet1.I1997;TRUE()) 

3.4.1 - Cell D1997 
SUM($Sheet1.B1997:C1997)<>$Sheet1.D1997 

---- 

4.0.0 - Cell A1997 
COUNTIF($Sheet2.$A$1:$A$75;$Sheet1.A300)>0 
IF(VLOOKUP($Sheet1.A300;$Sheet2.$H$2:$I$44;2;0)<>$Sheet1.I300;TRUE()) 

4.0.0 - Cell D1997 
SUM($Sheet1.IU300:IV300)<>$Sheet1.A300 

I have also uninstalled and reinstalled 4.0 a couple times, and it always does the same thing. 

I have attached a small test file created from the file I have been working on. The conditional formatting is located in cells A, D, and I. 

I should also not that this only appears to happen with the .xls extension. If I re-save the file as .ods in 3.4.1, the problem does not occur in 4.0.0.
Comment 1 Ariel Constenla-Haile 2013-07-26 19:31:52 UTC
Created attachment 81169 [details]
Screen shot: 3.4.1 vs. 4.0.0
Comment 2 Oliver Brinzing 2013-07-27 09:20:13 UTC
.
Comment 3 Rob Weir 2013-07-29 14:04:47 UTC
@Ariel, so are you confirming?  These screenshots are ones you have taken?   If so, feel free to mark the issue as CONFIRMED.
Comment 4 Ariel Constenla-Haile 2013-07-29 14:09:39 UTC
Confirming
(There are too many regression in Calc 4.0.0)
Comment 5 Oliver-Rainer Wittmann 2013-09-02 08:53:02 UTC
I will have a deeper investigation on this.
Comment 6 Clarence GUO 2013-09-02 10:06:15 UTC
Created attachment 81431 [details]
Comparison between MS Excel 2003 and 2010

I find a strange problem. Even the result of the same XLS is different between Excel 2003 and Excel 2010.
Open the sample by Excel 2003 and Excel 2010, you can find the result is completely different in column A, D and I. And select I2:I12, open conditional formatting, you can also find the referenced cell is completely different.
The result on AOO4.0 is more like assemble of that on Excel 2003 and Excel 2010, column A is like 2010, column D and I are like 2003
Comment 7 Oliver-Rainer Wittmann 2013-09-02 10:13:28 UTC
I have made the following strange observations:
- Opening the given sample file in Microsoft Excel 2010 and Microsoft Excel 2013 reveals the same results as in AOO 3.4.1, AOO 3.4.0 and OOo 3.3.0
- Opening the given sample file in Microsoft Excel 2007 reveals no conditional formatting. When trying the edit the imported conditional formatting rules Microsoft Excel 2007 tells me that references to other sheets are not allowed in conditional formatting rules.
- Opening the given sample file in Microsoft Excel 2003 reveals no conditional formatting in columns A and I. The one in D is the same as in AOO 4.0.0 - wrong cells are referenced. For the conditional formatting in columns A and I the same holds as for Microsoft Excel 2007
--> The different Microsoft Excel versions interpret the its binary format differently.
--> Our Microsoft Excel binary file format export supports features which are not available in Microsoft Excel 2003 and 2007

- I saved the given sample file in AOO 3.4.1 in ODF file format. Opening this document in AOO 4.0.0 and exporting it to Microsoft Excel binary format seems to create the same file as the given one.
--> Our Microsoft Excel binary file format export does not seem to be changed compared to the one in AOO 3.4.1

---> Our Microsoft Excel binary file format import seems to be changed compared to the one in AOO 3.4.1 - it looks like it is now more like the one in Microsoft Excel 2003/2007

@Pat: Do you know by which application the original given sample file had been created?
Comment 8 Oliver-Rainer Wittmann 2013-09-02 10:14:52 UTC
(In reply to Clarence GUO from comment #6)
> Created attachment 81431 [details]
> Comparison between MS Excel 2003 and 2010
> 
> I find a strange problem. Even the result of the same XLS is different
> between Excel 2003 and Excel 2010.
> Open the sample by Excel 2003 and Excel 2010, you can find the result is
> completely different in column A, D and I. And select I2:I12, open
> conditional formatting, you can also find the referenced cell is completely
> different.
> The result on AOO4.0 is more like assemble of that on Excel 2003 and Excel
> 2010, column A is like 2010, column D and I are like 2003

We have made more or less the same observation.
Comment 9 Oliver-Rainer Wittmann 2013-09-02 10:19:27 UTC
Created attachment 81432 [details]
simplified sample file

- Open this simplified sample file in AOO 3.4.1 and/or AOO 4.0.0 - it contains conditional formatting referencing the cells inclusive the sheet name.
- Export it to Microsoft Excel binary file format (*.xls)
- Open it in Microsoft Excel 2003, 2007, 2010 and/or 2013
--> you will see one part of my observation
Comment 10 Oliver-Rainer Wittmann 2013-09-02 10:24:25 UTC
Created attachment 81433 [details]
another simplified sample containing references to other sheets in the conditional formatting criteria
Comment 11 Pat 2013-09-03 01:56:48 UTC
@(In reply to Oliver-Rainer Wittmann from comment #7)
> @Pat: Do you know by which application the original given sample file had
> been created?

The file was originally created in Open Office, although I don't know what version for sure. We have been using the file for at least 3 years now.
Comment 12 Clarence GUO 2013-09-12 07:59:40 UTC
Once resave the sample by MS Excel without any change, reopen the sample by MS Excel, you can see all conditional formatting lost by both MS Excel 2003 and 2010. Then reopen this file by AOO3.4.0 or AOO4.0, now their results are same, don't show any number formats like that in Excel. So seems Excel doesn't support such formula usage on conditional formatting then Excel abandon them.

With Oliver's simple ODS, even resave to XLS by AOO3.4.0, I can see conditional formatting different between Excel 2003 and 2010. So the issue of conditional formatting difference on different Excel version is not introduced by AOO3.4.1.

Furthermore, I copy the cells from Oliver's simple ODS to Excel, and try to create same conditional formatting, but I failed on both Excel 2003 and 2010, no conditional formatting show on these cells. Maybe that can illustrate again that such formula usage on conditional formatting cannot support by Excel. But I'm not expert on conditional formatting, is anybody can confirm my conclusion?

If my conclusion is true, then there's feature difference between AOO and Excel. Then as Pat said the sample was converted from ODS, maybe in this case it is an invalid sample.
Comment 13 Andre 2014-02-24 14:48:51 UTC
Looks like this bug was introduced by SVN revision 1381452 for bug 117283 (adding filter support for functions AVERAGEIF, AVERAGEIFS, COUNTIFS, SUMIFS, XOR).
Comment 14 Andre 2014-02-24 14:54:12 UTC
Yes, undoing changes in sc/source/filter/excel/xicontent.cxx by converting back 

    ExcelToSc.Convert(..., FT_Conditional)

to

    ExcelToSc.Convert(..., FT_RangeName)

loads the bugdoc correctly.
Comment 15 Marcus 2017-05-20 10:45:02 UTC
Reset the assignee to the default "issues@openoffice.apache.org".