Bug 87142 - LibreOffice doesn't output the correct results for formula containing ranges when cells in the range are deleted (hard recalc needed)
Summary: LibreOffice doesn't output the correct results for formula containing ranges ...
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
(earliest affected) release
Hardware: All All
: medium major
Assignee: Not Assigned
Keywords: regression
Depends on:
Reported: 2014-12-09 08:33 UTC by halol
Modified: 2016-04-19 05:33 UTC (History)
2 users (show)

See Also:
Crash report or crash signature:

Sample spreadsheet to reproduce the problem (22.42 KB, application/vnd.oasis.opendocument.spreadsheet)
2014-12-09 08:33 UTC, halol

Note You need to log in before you can comment on or make changes to this bug.
Description halol 2014-12-09 08:33:22 UTC
Created attachment 110608 [details]
Sample spreadsheet to reproduce the problem


Some formula are producing the wrong results if the range they are referring to has been modified (insertion, deletion).
Changing the range fixes the problem, but reverting to the initial range leads to the same wrong results.

Steps to reproduce:

disclaimer: the scenario used to reproduce is the one I ran into, but it may be possible to reproduce the same issue with a simpler scenario.

Create two sheets in the document (see attached example).

The first sheet should contain a table where the column header (B1) is the name of the second sheet and the row headers (A2:A10 for example) are also row headers of the table/range in the second sheet.
Fill the table with the following formula: VLOOKUP( $A2; INDIRECT(B$1&".$A$2:$B$400"); 2; 0 )

On the second sheet create a simple table (A2:B20 for example) with any number of row, but make sure that the headers used in the sheet 1 are also used here.

Simply put, in the sheet 1 we just have a copy of some of the values from sheet 2.

Then start messing with the table on the sheet 2:
- insert two cells (shift the cells down) in the middle of the table with new header and value.
- repeat this a few times.
- then delete some of the newly inserted cells (shift cells up).

=> check the results in the sheet 1: some values should be incorrect (specially those located 'below' the cells that were inserted/deleted) and should display the values from other cells.

Then, change the range in the formula that produces the wrong results (for example $A$2:$B$300 instead of $A$2:$B$400) => that will fix the problem.
Then revert to the previous range in the formula => the problem should also reappear.

- with the sample I provide, I believe that you can reproduce simply by deleting the cells A6:B6 and A13:B13 ('NewValue' cells that were inserted).
- closing and re-opening the sample seem to fix the problem. With my original spreadsheet this isn't sufficient, I have to modify all the ranges.
Comment 1 raal 2014-12-10 07:14:27 UTC
I can confirm with LO 4.3.4, win7
After CTRL+SHIFT+F9 are values on Sheet1 correct.
Comment 2 halol 2014-12-10 08:58:16 UTC
I can confirm that ctrl-shift-F9 solves the problem even on more complex spreadsheets.
I tried F9 in the past without success, I didn't know that a hard recalc was possible with ctrl-shift-F9.
Thanks for the tip.
Comment 3 Timur 2015-07-23 15:18:22 UTC
Started in 4.2.x., regression. Looks like fixed in, probably related to fixing Bug 31577 or Bug 91411. 
I kindly ask you to test with that version, or with, also on a more complex spreadsheets.
If OK, please mark as Resolved-WorksForMe. If not, set New again.
Comment 4 mahfiaz 2016-04-19 05:33:42 UTC
I tested with, couldn't reproduce. Also as per Timur's comment and the age of this report I close this bug.