Created attachment 132138 [details] Four rows are adjusted correctly or not, as noted in the comments. This bug is so simple I wonder whether I misunderstand something but, if not, I think it's critical. It may be related to #42261 from Winfried Donkers. The attachment illustrates the problem. The four rows 6-9 containing value "1" are summed with =SUM($B$6:$B$9). If one right-clicks on row-5 "Add Rows Above" the formula is correctly adjusted to =SUM($B$7:$B$10). However the same operation on row-6 results in the formula =SUM($B$6:$B$10) which sums five rows. I would expect it to also adjust the range to $B$7:$B$10. This problem was first identified when a macro using: oActSheet.insertCells (oCellRangeAddress, com.sun.star.sheet.CellInsertMode.ROWS) failed to work as expected though in a more complex way. Regards, David Lochrin
did you try LibO 5.2.6? the 5.1.4 release you are using is obsolete
Yes, could you please try to reproduce it with the latest version of LibreOffice from https://www.libreoffice.org/download/libreoffice-fresh/ ? I have set the bug's status to 'NEEDINFO'. Please change it back to 'UNCONFIRMED' if the bug is still present in the latest version.
I'm still running an old version of Linux at the moment (SuSE 11.4) and am unable to run LibreOffice 5.3, though I plan to buy 64-bit hardware soon and upgrade. However I uninstalled LibreOffice-5.1.4.2 and performed a fresh install of all version 5.2.6.2 software (core packages, GB language pack & help pack, and the SDK). I have to report that the problem reported above occurs on 5.2.6.2 also. DL
Please review the option: Menu/Tools/Options/LibreOffice calc/General - Expand references when new columns/rows are inserted.
(In reply to m.a.riosv from comment #4) > Please review the option: > Menu/Tools/Options/LibreOffice calc/General - Expand references when new columns/rows are inserted. You're absolutely correct Miguel, and I apologise for wasting everyone's time. I thought it was a bit too simple for a bug! However I do think this option is rather dangerous because it can obviously lead to incorrect results when a spreadsheet designer is unaware of it and a function argument then operates on unintended cells. I imagine most would not be aware of it; I wasn't and I count myself as a knowledgable user. Should it be set "off" by default, and has the default changed in recent versions? Though I realise changing defaults is tricky. In my case the effect arose quite indirectly. I have written macros for adding & deleting multiple rows and sorting up to two columns of commonly formatted sheets (with header row(s), a data area, and an end row), and I've been using them for years. But recently a calculation located below the data area (i.e. outside the nominal scope of the macros) generated incorrect results when rows were added anywhere in the data such that the last row of the new data area extended at least to the row ("x") immediately before a SUM(x:y) function. The SUM() function then summed the range (x:y+n) where "n" is the number of rows added. Thanks for your response. Regards, David Lochrin
(In reply to David Lochrin from comment #5) As more options we have becomes more complicated deal with them. You are right change defaults, it's usually a hard decision. > > But recently a calculation located below the data area (i.e. outside the > nominal scope of the macros) generated incorrect results when rows were > added anywhere in the data such that the last row of the new data area > extended at least to the row ("x") immediately before a SUM(x:y) function. > The SUM() function then summed the range (x:y+n) where "n" is the number of > rows added. Please attach a sample file with a step by step about how to reproduce the issue.
I've attached a sheet containing the AddRow macro which demonstrates the problem (with DelRow & Sort). This also contains a note describing how to reproduce it. Please note that my previous description of the condition which manifests the problem was not quite accurate, but not in any fundamental way. As a more philosophical observation, I'd also like to say I think the option “Expand references when new columns / rows are added” is not consistent with absolute cell referencing using the "$" syntax. I'd expect an absolute cell range like $A$5:$A$10 to refer to those particular cells regardless of what rows or columns were added outside the range. With this option enabled, however, a user could unintentionally introduce a "bug" into an existing macro without even being aware that it was a possibility. In effect the concept of an absolute cell reference should be extended by adding the words "...providing the option to expand references is not enabled." DL
Created attachment 132168 [details] The AddRow macro in this sheet demonstrates the problem Here's the attachment for the previous comment...
I do not reproduce the problem with the steps described in bug description: if I insert a row above row 5 or row 6, in both cases the formula is updated as SUM($B$7:$B$10) as expected. In my user profile the option "Expand references when new columns/rows are inserted" is not checked. Tested with Version: 6.0.4.2 Build ID: 1:6.0.4~rc2-0ubuntu0.16.04.1 CPU threads: 4; OS: Linux 4.4; UI render: default; VCL: gtk3; Locale: fr-FR (fr_FR.UTF-8); Calc: group threaded Best regards. JBF
I can't reproduce the error. (default options) The bug was tested in 2 versions: Version 4.0.0.1 (Build ID: 527dba6f6e0cfbbc71bd6e7b88a52699bb48799) Version: 6.0.5.1 Build ID: 0588a1cb9a40c4a6a029e1d442a2b9767d612751 CPU threads: 2; OS: Linux 4.15; UI render: default; VCL: gtk2; Locale: es-AR (es_AR.UTF-8); Calc: group
(In reply to malboarg from comment #10) > I can't reproduce the error. (default options) > The bug was tested in 2 versions: > > Version 4.0.0.1 (Build ID: 527dba6f6e0cfbbc71bd6e7b88a52699bb48799) > > Version: 6.0.5.1 > Build ID: 0588a1cb9a40c4a6a029e1d442a2b9767d612751 > CPU threads: 2; OS: Linux 4.15; UI render: default; VCL: gtk2; > Locale: es-AR (es_AR.UTF-8); Calc: group I apologise for any confusion which has occurred in relation to this, but it was cleared up in Comment-4 by m.a.riosv: > Please review the option: > Menu/Tools/Options/LibreOffice calc/General - Expand references when new columns/rows are inserted. which I acknowledged in Comment-5: > You're absolutely correct Miguel, and I apologise for wasting everyone's time. I thought it was a bit too simple for a bug! I assumed I'm not authorised to close a bug report. David Lochrin