Created attachment 143645 [details] Simple sheet with start conditions for producing error I created the following simple sheet sheet In 2 columns, A and D, enter the numbers 1 to 20 In cell B 20 enter =SUM(A10:A19) In cell C20 enter =SUM(D10:D19) See attached sheet. Save the sheet, Close Calc and then reopen the sheet. Select cells B20:C20 Copy and select range b1:c20 and paste. ie copy the formulas of the 2 selected cells upwards to the top of the sheet. As you would expect some cells show a REF error due to formulas referencing cells before row 1. Now insert a column between columns A and B. Select column B, right click in column header and insert col left. (I also tried shift cells right) All of the cells in columns B and C now have a REF error. In column C the value displayed is #REF!. In Col B #REF! is displayed in the formula but cells still show a numeric value
ctrl+maj+F9 -> #REF! everywhere in columns C and D. Not sure if there is a bug there. Best regards. JBF
I have not the issue, with autocalculate on: Version: 6.0.6.1 (x64) Build ID: dc68ad6c5cde3c62874b96422f2e5e8252499bad CPU threads: 4; OS: Windows 10.0; UI render: default; Locale: es-ES (es_ES); Calc: CL
(In reply to Jean-Baptiste Faure from comment #1) > ctrl+maj+F9 -> #REF! everywhere in columns C and D. > > Not sure if there is a bug there. > > Best regards. JBF Like you after ctrl+shift+F9 I get #REF in cols C and D. No need to save sheet if I do this. However the fact that column C and D are filled with #REF is wrong . Many of these cells (row 11 downwards) are valid. The #REF behaviour is also inconsistent. For example if I only copy the cells upwards to row 10 then, as expected row 10 cells contain #REF but if I then insert a column and use ctrl+shift+F9 then I get #REF in all column C cells but the column D cells remain correctly calculated.
(In reply to m.a.riosv from comment #2) > I have not the issue, with autocalculate on: > Version: 6.0.6.1 (x64) > Build ID: dc68ad6c5cde3c62874b96422f2e5e8252499bad > CPU threads: 4; OS: Windows 10.0; UI render: default; > Locale: es-ES (es_ES); Calc: CL I also have autocalc on. All my settings should be at default. I just installed this version of lbreoffice.
(In reply to m.a.riosv from comment #2) > I have not the issue, with autocalculate on: > Version: 6.0.6.1 (x64) > Build ID: dc68ad6c5cde3c62874b96422f2e5e8252499bad > CPU threads: 4; OS: Windows 10.0; UI render: default; > Locale: es-ES (es_ES); Calc: CL Just spotted your version number. I'll have to try that one too. And the other pre release 6.1
Repeated bugs in pre release version 6.1.0.2 In this instance I did not save a file. Just created a new sheet, entered the data and sum(..) formula and copied up to row 1. Then inserted a column and pressed ctrl-shift-F9. Cols C and D fill with #REF
Pinged Eike on IRC: "It's a bug; D11:D20 shouldn't be ref-errors after inserting a column. Likely yet another shared formula grouping problem."
error reproducible in 6.3.0.0.alpha0+ 2019-03-22, not in 4.1.6.2, maybe related to the overall 'shared formula broken' issue, imho it's critical that somebody cares for this, a spreadsheet should be reliable in all respects, reg. b.
funny - silly - wrong - and still virulent in: Version: 6.5.0.0.alpha0+ (x64) Build ID: 209fc9fd7fa433947af0bf86e210d73fa7f5a045 CPU threads: 8; OS: Windows 6.1 Service Pack 1 Build 7601; UI render: default; VCL: win; Locale: de-DE (de_DE); UI-Language: en-US Calc:
still a bug in 6.4.0.1, probably related to other #ref-errors, funny side-effect: cells C11:C20 show a correct result, despite when you edit the formula inside it's announced as '=SUM(A#REF!:A#REF!)', iterations on, severe: inserting column trashes data, tested with: Version: 6.4.0.1 (x64) Build ID: 1b6477b31f0334bd8620a96f0aeeb449b587be9f CPU threads: 8; OS: Windows 6.1 Service Pack 1 Build 7601; UI render: default; VCL: win; Locale: de-DE (de_DE); UI-Language: en-US Calc:
Started in 4.2.
Started in 4.2 and should be bibisectable with 43max.
I did bibisect with 43max up to step "Select column B, right click in column header and insert col left". There were 2 changes. First from (what should be) good to bad1: 592049de352d2fd0fe256fe499a2edd175c2bb0b is the first bad commit commit 592049de352d2fd0fe256fe499a2edd175c2bb0b Author: Matthew Francis <mjay.francis@gmail.com> Date: Thu May 28 19:46:09 2015 +0800 source-hash-f32df2d590d0ee14f09664934457ba9e8de8cbe6 commit f32df2d590d0ee14f09664934457ba9e8de8cbe6 Author: Kohei Yoshida <kohei.yoshida@collabora.com> AuthorDate: Fri Feb 28 21:25:01 2014 -0500 Commit: Kohei Yoshida <kohei.yoshida@collabora.com> CommitDate: Fri Feb 28 21:28:57 2014 -0500 fdo#75053: Adjust reference update on shift for formula groups. This is similar to my earlier fix for reference update on moving of cells. Change-Id: I592599507bfcab12f611eeae7b56c99da6c31919 Previous source-hash-aa6c5b7faecdb57cbdeac051e304531c1a1cf63b. https://gerrit.libreoffice.org/plugins/gitiles/core/+/f32df2d590d0ee14f09664934457ba9e8de8cbe6%5E!/
Second from bad1 to bad2: d6977db2031fb27e2805f10f4db1b8b47eb76133 is the first bad commit commit d6977db2031fb27e2805f10f4db1b8b47eb76133 Author: Matthew Francis <mjay.francis@gmail.com> Date: Thu May 28 19:50:56 2015 +0800 source-hash-d658c092f488fc0d4cb924fe3e34cab997db76e2 commit d658c092f488fc0d4cb924fe3e34cab997db76e2 Author: Kohei Yoshida <kohei.yoshida@collabora.com> AuthorDate: Fri Mar 7 18:03:24 2014 -0500 Commit: Kohei Yoshida <kohei.yoshida@collabora.com> CommitDate: Fri Mar 7 18:05:07 2014 -0500 fdo#75628: SUM should inherit error if one is present in its references. Change-Id: I94017fe91295dbb929f57be5e3fb26edf5032a8f Previous source-hash-78e6b7a94265507e43dd80182706970f49cdb303. https://gerrit.libreoffice.org/plugins/gitiles/core/+/d658c092f488fc0d4cb924fe3e34cab997db76e2%5E!/
Dear MD, To make sure we're focusing on the bugs that affect our users today, LibreOffice QA is asking bug reporters and confirmers to retest open, confirmed bugs which have not been touched for over a year. There have been thousands of bug fixes and commits since anyone checked on this bug report. During that time, it's possible that the bug has been fixed, or the details of the problem have changed. We'd really appreciate your help in getting confirmation that the bug is still present. If you have time, please do the following: Test to see if the bug is still present with the latest version of LibreOffice from https://www.libreoffice.org/download/ If the bug is present, please leave a comment that includes the information from Help - About LibreOffice. If the bug is NOT present, please set the bug's Status field to RESOLVED-WORKSFORME and leave a comment that includes the information from Help - About LibreOffice. Please DO NOT Update the version field Reply via email (please reply directly on the bug tracker) Set the bug's Status field to RESOLVED - FIXED (this status has a particular meaning that is not appropriate in this case) If you want to do more to help you can test to see if your issue is a REGRESSION. To do so: 1. Download and install oldest version of LibreOffice (usually 3.3 unless your bug pertains to a feature added after 3.3) from https://downloadarchive.documentfoundation.org/libreoffice/old/ 2. Test your bug 3. Leave a comment with your results. 4a. If the bug was present with 3.3 - set version to 'inherited from OOo'; 4b. If the bug was not present in 3.3 - add 'regression' to keyword Feel free to come ask questions or to say hello in our QA chat: https://web.libera.chat/?settings=#libreoffice-qa Thank you for helping us make LibreOffice even better for everyone! Warm Regards, QA Team MassPing-UntouchedBug
Hello Kohei. Please take a look and see if this is your regression. Putting LO adrress in CC, not sure which one is correct, there are two.