Created attachment 110608 [details] Sample spreadsheet to reproduce the problem Overview: 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. Notes: - 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.
I can confirm with LO 4.3.4, win7 After CTRL+SHIFT+F9 are values on Sheet1 correct.
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.
Started in 4.2.x., regression. Looks like fixed in 4.4.5.1, probably related to fixing Bug 31577 or Bug 91411. I kindly ask you to test with that version, or with 4.4.5.2, also on a more complex spreadsheets. If OK, please mark as Resolved-WorksForMe. If not, set New again.
I tested with 5.0.2.0, couldn't reproduce. Also as per Timur's comment and the age of this report I close this bug.