https://bugs.documentfoundation.org/show_bug.cgi?id=104711
Bug ID: 104711
Summary: Relative named range in formula not updating
Product: LibreOffice
Version: 5.4.0.0.alpha0+
Hardware: x86-64 (AMD64)
OS: Linux (All)
Status: UNCONFIRMED
Severity: normal
Priority: medium
Component: Calc
Assignee: [email protected]
Reporter: [email protected]
Created attachment 129688
--> https://bugs.documentfoundation.org/attachment.cgi?id=129688&action=edit
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.
--
You are receiving this mail because:
You are the assignee for the bug._______________________________________________
Libreoffice-bugs mailing list
[email protected]
https://lists.freedesktop.org/mailman/listinfo/libreoffice-bugs
