Bug 128110 - #REF! error in running total formula after delete of row
Summary: #REF! error in running total formula after delete of row
Status: CLOSED NOTABUG
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
Inherited From OOo
Hardware: All All
: medium normal
Assignee: Not Assigned
URL:
Whiteboard:
Keywords:
Depends on:
Blocks:
 
Reported: 2019-10-12 17:18 UTC by Al Barth
Modified: 2019-10-14 09:27 UTC (History)
5 users (show)

See Also:
Crash report or crash signature:

Attachments
example spreadsheet (8.96 KB, application/vnd.oasis.opendocument.spreadsheet)
2019-10-12 17:20 UTC, Al Barth 		Details
screenshot (126.36 KB, image/png)
2019-10-13 06:02 UTC, Oliver Brinzing 		Details
Sample file with Named Expression with relative references. (11.16 KB, application/vnd.oasis.opendocument.spreadsheet)
2019-10-13 21:27 UTC, m_a_riosv 		Details
View All Add an attachment (proposed patch, testcase, etc.)

Note You need to log in before you can comment on or make changes to this bug.
Description Al Barth 2019-10-12 17:18:41 UTC 
Description:
A simple spreadsheet with running total formula in col B. Beginning on row 2, col A contains numbers and col B has formula to take previous row total (B) and add current number in cell (A). Deleting a row results in #REF! error in col B cell of said row and below. 

Steps to Reproduce:
1. Place formula =B1+A2 into cell B2.
2. Copy formula from B2 down for several rows such that cell B3 contains formula =B2+A3, etc.
3. Place values into cells in col A.
4. Col B cells have running total.
5. Now delete row 3 for example.
6. Afterward cell B3 shows #REF!
7. Select cell B3 and formula bar correctly showns as =B2+A3 and correctly color highlights reference cells of formula


Actual Results:
10	10
38	#REF!
90	#REF!

Expected Results:
Expect cells with #REF! to correctly recalculate as formula cell references are dynamic (relative) and thus still valid.


Reproducible: Always


User Profile Reset: Yes



Additional Info:
Version: 6.3.2.2 (x64)
Build ID: 98b30e735bda24bc04ab42594c85f7fd8be07b9c
CPU threads: 2; OS: Windows 10.0; UI render: default; VCL: win; 
Locale: en-US (en_US); UI-Language: en-US
Calc: threaded
Comment 1 Al Barth 2019-10-12 17:20:31 UTC 
Created attachment 154962 [details]
example spreadsheet

this spreadsheet still shows problem, even after save/reopen.
Comment 2 Roman Kuznetsov 2019-10-12 20:32:06 UTC 
I confirm behavior from description, but I'm not sure it's a bug.

Eike, Mike, any opinion here?
Comment 3 Oliver Brinzing 2019-10-13 06:02:05 UTC 
Created attachment 154964 [details]
screenshot

the behaviour is the same way as exel does
Comment 4 m_a_riosv 2019-10-13 21:27:10 UTC 
Created attachment 154981 [details]
Sample file with Named Expression with relative references.

IMO not a bug, if a row/column is deleted then cells with references to them lost the references and show error instead (#REF!) the row/column.

But it's possible to get it by using Named Expressions with relative references.
Comment 5 Eike Rathke 2019-10-14 09:27:43 UTC 
Normal spreadsheet behaviour, not a bug. If cells/rows/columns are deleted that entirely encompass a reference that reference is invalidated.
If you want "floating" (or whatever one might call that) references then use a named expression, e.g. on cell B2 define name RELREF to B1+A2 and use =RELREF as formula in B2 and copy down.