Created attachment 129688 [details] Example of formula Pages 74 and 75 of the book ‘Professional Excel Development’, by Bovey, Wallentin, Bullen and Green (Addison-Wesley, 2009, ISBN 978-0321508799) describe a method to create formulae that automatically adjust the range they refer to when a row is inserted directly above them. This uses a relative named range that always points to the cell above the cell that contains the formula. For example “=SUM(A1:CellAbove)”. In LibreOffice Calc, version 5.1.4.2, such a formula gives the correct result when first entered but doesn’t update when a row is inserted above it and a number typed in to the cell above the formula. Pressing F9 doesn’t make it update either. I have tried this on Ubuntu Gnome 16.04 and on Windows 7. The method works correctly in Apache OpenOffice Calc version 4.1.2, so this isn’t a request to blindly copy Excel for the sake of it. It looks like LibreOffice Calc is meant to be able to do this but that it doesn’t work properly, and that the bug may have been introduced after the application was forked from OpenOffice. It is possible to make such a formula update by opening the ‘Manage Names’ dialogue (Ctrl+F3) and changing something, such as the scope from global to sheet and back again, but this involves more key-presses and mouse-clicks that it would to update a conventional formula by hand. Also, you can’t expect a client to do this on a spreadsheet you have created for them. A possible work around involves creating a relative named range covering the whole of the range, e.g. ‘RangeAbove’ for the range e.g. A$1:A5, and then entering “=SUM(RangeAbove)”. This works as desired but requires different named ranges for different sized ranges or for ranges that start on a different row, whereas the original method only needs one named range that can be used in all cases. The problem appears when using a named range as part of a range address e.g. A1:NamedCell. If this feature could be made to work properly it would be extremely useful.
Reproducible with: Version: 5.3.0.0.beta2 (x64) Build ID: a7e30712ad6d8bc9286007b37aa581983e0caba3 CPU Threads: 4; OS Version: Windows 6.19; UI Render: default; Layout Engine: new; Locale: es-ES (es_ES); Calc: group but not with: Version: 5.2.4.1 (x64) Build ID: 9b50003582f07ac674d6451e411e9b77cccd2b22 CPU Threads: 4; OS Version: Windows 6.19; UI Render: default; Locale: es-ES (es_ES); Calc: group
Investigating.
Eike Rathke committed a patch related to this issue. It has been pushed to "master": http://cgit.freedesktop.org/libreoffice/core/commit/?id=439b2a134218b93e6ca9fa23005b89c19498f586 Resolves: tdf#104711 adjust range reference constructed of named anchors It will be available in 5.4.0. The patch should be included in the daily builds available at http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: http://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.
Will go to 5.3.0 as well and pending review for 5-2 at https://gerrit.libreoffice.org/32924
Eike Rathke committed a patch related to this issue. It has been pushed to "libreoffice-5-3": http://cgit.freedesktop.org/libreoffice/core/commit/?id=540e9f5f290b5801c10fa3a6e3ad9046607dcd9c&h=libreoffice-5-3 Resolves: tdf#104711 adjust range reference constructed of named anchors It will be available in 5.3.0.2. The patch should be included in the daily builds available at http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: http://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.
Eike Rathke committed a patch related to this issue. It has been pushed to "libreoffice-5-2": http://cgit.freedesktop.org/libreoffice/core/commit/?id=33c1491acb3fd6b87ac16d80ed728f423762e867&h=libreoffice-5-2 Resolves: tdf#104711 adjust range reference constructed of named anchors It will be available in 5.2.5. The patch should be included in the daily builds available at http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: http://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.