Description: Error Type One: inserting one row above referenced range results in one bad function range. Error Type Two: inserting multiple rows above referenced range results in all bad function ranges. The following functions will result in these two errors: 'insert entire rows above' 'insert entire rows below' 'shift cells down' above referenced range 'shift cells up' above referenced range The following functions will not result in these errors: 'shift cells down' above cells containing function 'shift cells up' above cells containing function 'delete entire rows' 'shift cells right' 'insert or delete column' Steps to Reproduce: Error Type One: inserting one row above referenced range 1. Copy formula =sum(A1:A4) to first ten cells in column B. 2. Highlight entire row 1 by right click on 1 label then 'insert rows above'. Error Type Two: inserting multiple rows above referenced range Copy formula =sum(A1:A4) to first ten cells in column B. Highlight cel1s A1:B2 then right click then 'insert' 'shift cells down' ok. Actual Results: Error Type One Result: function in B2 is wrong =sum(A1:A5) Note: function in B3 is correct =sum(A3:A6) with the rest cells correct. Error Type Two Result: function in B2 is wrong =sum(A1:A6) Note: function in B3 is wrong =sum(A1:A7) with the rest cells wrong Expected Results: Type One: function in B2 correct =sum(A2:A5) Type Two: function in B2 correct =sum(A3:A6) function in B3 correct=(A4:A7) ect. Reproducible: Always User Profile Reset: Yes OpenGL enabled: Yes Additional Info: Libre office is an amazing open source accomplishment and I commend everyone who has worked on this collabertive effort. This problem did not exist in older versions of calc. I did not catch this error in my calculations for more than six months and it has cost me financially. I always used the older tested business version. Moving referenced ranges a is such a basic, core feature, central to any spreadsheet program, it seems crazy that I might be the first person to discover an error this serious. I can no longer trust the code and will return to commercial software. Maybe try again in the future with better bug testing.
Thanks for the bug report, please attach a sample spreadsheet with exact reproduction steps.
For the record, if you were working on an XLSX file, bug 128894 might be the same issue, checking whether it still occurs in a daily build would be good: https://dev-builds.libreoffice.org/daily/ On a side note, it is unfortunate that this bug costed you, and it seems serious, at the same time if users don't encounter and report it, then one can hardly expect it to get fixed. The code is accompanied by plenty of unit tests, however those can't catch everything, either.
Created attachment 156539 [details] Bug 129358 - CALC: Inserting or deleting cells above a referenced range results incorrect adjustments to the range witin functions.
This problem exists with both xlsx and ods files. It is different from bug 128894 which I have not experienced.
Thanks for the sample! I'm not seeing any issues, though, when I insert a whole row above the 1st, B2 is '=SUM(A2:A5)', etc. Similarly, when I insert in place of A1:B2, and shift them down, B3 is '=SUM(A3:A6)'. In this case B2 isn't filled, I assume there's a typo in the instructions. Tested both in 6.2.0.3 and 6.3.0.4 with the same results. Can you please paste the content of your About box here?
Version: 6.3.4.2 (x64) Build ID: 60da17e045e08f1793c57c00ba83cdfce946d0aa CPU threads: 4; OS: Windows 10.0; UI render: default; VCL: win; Locale: en-US (en_US); UI-Language: en-US Calc: CL
This error was also occurring for me under 6.2.8 I am running Windows 1903 on an AMD A10-9620P
(In reply to nslehcim from comment #7) This error was also occurring for me under LibreOffice 6.2.8 I am running Windows 10 Home ver 1903 build 18362.535 on an AMD A10-9620P You are correct about the typo; B2 should have been written as B3.
(In reply to nslehcim from comment #8) Error type two Result: function in B3 is wrong =sum(A1:A6) Note: function in B4 is wrong =sum(A2:A7) with the rest cells wrong
I also tried various ways of doing the same thing: using menus, right click; short cut keys, highlighting cell or entire row, saving and reopening the spreadsheet, different spreadsheets; all with the same result. I first discovered this issue a week ago but my backups copies formulas started getting corrupted over six months ago. I use this computer only for basic software; no games, experimental or risky software. Just windows, garmin, Libre Office and Firefox. Why would I be the only one with this issue?
Maybe I'm wrong but I think the issues is having the option Menu/Tools/Options/LibreOffice calc/General - Expand references when new columns/rows are inserted, enable, disable it and should work as you like. Closed as notabug, please reopen it if you are not agree.