Bug 104711 - Relative named range in formula not updating
Summary: Relative named range in formula not updating
Status: RESOLVED FIXED
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
5.1.4.2 release
Hardware: All All
: high major
Assignee: Eike Rathke
URL:
Whiteboard: target:5.4.0 target:5.3.0.2 target:5.2.5
Keywords: regression
Depends on:
Blocks:
 
Reported: 2016-12-16 11:47 UTC by Howard Rudd
Modified: 2017-01-11 22:16 UTC (History)
1 user (show)

See Also:
Crash report or crash signature:
Regression By:


Attachments
Example of formula (8.07 KB, application/vnd.oasis.opendocument.spreadsheet)
2016-12-16 11:47 UTC, Howard Rudd
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Howard Rudd 2016-12-16 11:47:35 UTC
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.
Comment 1 m.a.riosv 2016-12-16 21:59:46 UTC
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
Comment 2 Eike Rathke 2017-01-06 23:24:00 UTC
Investigating.
Comment 3 Commit Notification 2017-01-10 10:45:15 UTC
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.
Comment 4 Eike Rathke 2017-01-10 11:08:40 UTC
Will go to 5.3.0 as well and pending review for 5-2 at https://gerrit.libreoffice.org/32924
Comment 5 Commit Notification 2017-01-10 20:00:03 UTC
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.
Comment 6 Commit Notification 2017-01-11 22:16:45 UTC
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.