Issue 116574

Summary: Calc not expanding references correctly
Product: Calc Reporter: bfallert <bkfallert>
Component: formattingAssignee: AOO issues mailing list <issues>
Status: CONFIRMED --- QA Contact:
Severity: Trivial    
Priority: P3 CC: issues, oliver.brinzing, villeroy
Version: 3.4.1   
Target Milestone: ---   
Hardware: Mac   
OS: Mac OS X 10.6   
Issue Type: DEFECT Latest Confirmation in: ---
Developer Difficulty: ---
Attachments:
Description Flags
Cell Reference Expansion Bug none

Description bfallert 2011-01-23 15:00:47 UTC
This issue was discovered in a spreadsheet when a column summation uses cells from the top row down 
and from a mid-column row to the bottom of the column.

Example:  Spreadsheet cell F7 needs to calculate SUM(F3:F6).  Cell F15 needs to calculate SUM(F7:F14).  
The preference "Expand references when new columns/rows are inserted" is unchecked.

Insert row above row 7.

Cell F8 now shows SUM(F3:F6), which is no change.  Cell F16 now shows SUM(F8:F15), which shows that 
all the cell references shifted down due to the row insertion.

UNDO the insertion to reset the cell references.

Change the preference "Expand references when new columns/rows are inserted" to be checked.

Insert row above row 7.

Cell F8 now shows SUM(F3:F7), which is correct.  The cell references were expanded due to the row 
insertion.  Cell F16 now shows SUM(F7:F15), which is incorrect.  It should show SUM(F8:F15), having 
expanded the cell references down due to the row insertion.

If this bug has been reported in error, please explain.  Thanks.
Comment 1 bfallert 2011-01-23 15:06:05 UTC
Created attachment 75618 [details]
Cell Reference Expansion Bug
Comment 2 Marcus 2011-01-24 11:57:32 UTC
due to the rules not a P1
Comment 3 Oliver Brinzing 2011-01-24 17:33:10 UTC
.
Comment 4 Olga Plyasunova 2013-01-11 17:27:11 UTC
I just clarified steps
 
1. Open attached document.
2. Uncheck the preference "Expand references when new columns/rows are inserted" (For mac: OpenOffice.org -> Preferences -> OpenOffice Calc -> General).
3. Insert row above row 7.
4. Verify Cell F8 shows SUM(F3:F6). Cell F16 shows SUM(F8:F15).
5. Undo the row insertion
6. Change the preference "Expand references when new columns/rows are inserted" to be checked.
7. Insert row above row 7.

Actual result: Cell F8 shows SUM(F3:F7), Cell F16 shows SUM(F7:F15).  

Expected result: Cell F8 shows SUM(F3:F7), Cell F16 shows SUM(F8:F15).
Comment 5 Marcus 2017-05-20 11:33:39 UTC
Reset assigne to the default "issues@openoffice.apache.org".
Comment 6 Andreas S├Ąger 2019-11-16 22:05:35 UTC
This is the correct behaviour since the beginning of OOo.

WITHOUT option "Expand References"
-- any insertion at the first row of the referenced range shifts down the reference.
-- any insertion directly below the referenced just inserts cells _below_ the reference without affecting the reference.

WITH option "Expand References"
-- any insertion at the first row of the referenced range expands the reference.
-- any insertion directly below the referenced expands the reference.

In both cases any insertion between row #2 and the last row expands the reference.