Issue 66441 - conditional formatting error on inserting line
Summary: conditional formatting error on inserting line
Status: CLOSED DUPLICATE of issue 4155
Alias: None
Product: Calc
Classification: Application
Component: formatting (show other issues)
Version: OOo 2.0
Hardware: PC Windows XP
: P3 Trivial (vote)
Target Milestone: ---
Assignee: spreadsheet
QA Contact: issues@sc
URL:
Keywords: ms_interoperability, oooqa
Depends on:
Blocks:
 
Reported: 2006-06-14 19:09 UTC by bobq
Modified: 2006-06-30 09:43 UTC (History)
2 users (show)

See Also:
Issue Type: DEFECT
Latest Confirmation in: ---
Developer Difficulty: ---


Attachments
Excel file with conditional formatting showing problem (18.00 KB, application/vnd.ms-excel)
2006-06-16 17:19 UTC, bobq
no flags Details
Excel file with conditional formatting showing problem (18.00 KB, application/vnd.ms-excel)
2006-06-16 17:20 UTC, bobq
no flags Details
Simple test case illustrating referencing problem (8.32 KB, application/vnd.sun.xml.calc)
2006-06-16 17:59 UTC, atdsm
no flags Details

Note You need to log in before you can comment on or make changes to this issue.
Description bobq 2006-06-14 19:09:53 UTC
Hi,
Couldn't find previous reference to problem.
Cell references in conditional formatting conditional tests don't update when 
a line is inserted at line number smaller than line number of conditional 
formatting cell.
Spreadsheet originally created with Excel 2003.
Thanks,
Bob Q.
Comment 1 atdsm 2006-06-14 20:53:22 UTC
Can't confirm. I created a spreadsheet, made a cell with conditional formatting
dependant on another cell, then inserted lines before the cell pair, after the
cell pair, and in between the cell pair. In all three cases the cell reference
tracked correctly. (I am using 2.0.3-RC5 Windows XP, SP2.)

Could you please attach the Excel spreadsheet and a step-by-step procedure for
duplicating the problem?

Thanks,

Steve
Comment 2 bobq 2006-06-16 17:19:55 UTC
Created attachment 37199 [details]
Excel file with conditional formatting showing problem
Comment 3 bobq 2006-06-16 17:20:33 UTC
Created attachment 37200 [details]
Excel file with conditional formatting showing problem
Comment 4 bobq 2006-06-16 17:20:58 UTC
OK,
I attached the excel file where I noticed the problem.  Here are the steps...
Open in Calc.
Check conditional formatting in cell D153-it refers to cell C131.
Insert 2 rows at row 81.
Check cell D155 (was D153)-formatting still refers to C131, did not update to 
cell C133.
Thanks,
Bob Q. 
Comment 5 atdsm 2006-06-16 17:57:28 UTC
Confirmed with your spreadsheet.

Also, I managed to narrow down the problem. (There are actually two things which
happen, both of which must occur to cause a break in functionality.)

1) When importing from MS Excel, OOo Calc inteprets the sheet name for
conditional formatting as relative. (This, I think, is by design. Excel by
default does not put in a sheet name when referring to cells in the current
sheet, enabling conditionally formatted cells to copy and paste between sheets
correctly. Therefore, OOo keeps the sheet name relative, enabling the same sort
of copy paste operations to occur correctly.)

2) When a relative reference for sheet name is used, OOo Calc does not update
conditional formatting references when inserting cells/rows/columns. This
applies whether the conditional formatting is referring to its own or another sheet.

By relative, I mean using a cell reference without a $, ie:
"Sheet1.C1" is a relative reference.

An absolute reference would be:
"$Sheet1.$C$1"

By default, OOo Calc inserts absolute references when using conditional
formatting (prefixing the sheet name with the dollar sign ($)). If absolute
references are used, the conditional formatting references update when
cells/rows/columns are inserted.




Comment 6 atdsm 2006-06-16 17:59:50 UTC
Created attachment 37201 [details]
Simple test case illustrating referencing problem
Comment 7 atdsm 2006-06-16 18:04:57 UTC
To illustrate problem:

1) Open the "Simpler Test Case.ods"
2) Insert a row before row 5

The cell using conditional formatting with an absolute sheet reference (sheet
name preceded by $) updates properly. The cell using conditional formatting with
a relative sheet reference (sheet name not preceded by $) does not update properly.

Workaround: Use only absolute sheet references in conditional formatting. (Go
back and add $ to the front of all conditional formatting sheet references).

Drawback: This makes moving conditional formatting from one sheet to another
very difficult, because any copy/paste operation will reference the original
sheet, not the new sheet.
Comment 8 frank 2006-06-30 09:43:22 UTC
Hi,

please have a look at Issue 3991 and Issue 4155 . Both are related and I close
this one as double to Issue 4155.

Frank

*** This issue has been marked as a duplicate of 4155 ***
Comment 9 frank 2006-06-30 09:43:51 UTC
closed double