Apache OpenOffice (AOO) Bugzilla – Issue 116574
Calc not expanding references correctly
Last modified: 2019-11-16 22:05:35 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.
Created attachment 75618 [details] Cell Reference Expansion Bug
due to the rules not a P1
.
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).
Reset assigne to the default "issues@openoffice.apache.org".
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.