Natural named range not updated

[Tero Karvinen]

Binary package hint: openoffice.org2-calc

Results of formulas using natural named range are not always updated automatically.

To reproduce, in OpenOffice calc,
1) create a row with cells "event" and "amount"
2) add some events ("foo") and numbers (2)
3) create last row "total", "=SUM(amount)"
Now total row correctly displays sum of amounts.
4) insert a row before "total" row
5) add to new row "plane ticket" "1000"
What happens
- total row still shows the old amount (without +1000)
What should happen
- total row should show the correct sum of amounts, including the 1000 of "plane ticket"

Tested with Breezy and openoffice.org2-calc 1.9.129-0.1ubuntu4.

[Tero Karvinen (karvinen+launchpad)]

A screenshot of a spreadsheet showing the error.

[Tero Karvinen (karvinen+launchpad)]

Spreadsheet where natural named range is not updated. Same as in screenshot.

[Tero Karvinen (karvinen+launchpad)]

It seems that update happens when saved file is loaded. Thus, the attached spreadsheet does not show the problem when opened. You can still reproduce it by inserting another row.

[Chris Cheney (ccheney)]

Confirmed on Ubuntu 7.10 OpenOffice 2.3.0.

[Bert Vorenholt (bert-vorenholt-nl)]

Confirmed on Kubuntu 7.10 OOo 2.3.0

Not only a named-range problem though. Even with a "normal" =SUM() formula the col/row range isn't updated.
When you actually do the insert on the row above the row with the formula (inserting a row anywhere within the range, except a new last one), the range gets updated, but then you have a gap in the table.

I find this bug extreemly important because this is not expected and easy overseen, with possible (financial) consequences for the user who is using OOo for (professional) calculations/analyzing/reporting.

[Bert Vorenholt (bert-vorenholt-nl)]

Oops... this seems not to be a bug, but a feature :-(

Searching the OOo Calc forum topic, i saw the next solution:

http://www.oooforum.org/forum/viewtopic.phtml?t=59440 (answer on question)

<begin solution>
JohnV
Administrator

Joined: 07 Mar 2003
Posts: 4810
Location: Kentucky, USA

Posted: Sun Jul 08, 2007 12:25 pm Post subject:
Try Tools > Options > Calc > General
From Help:
Quote:
Expand references when new columns/rows are inserted
Specifies whether to expand references when inserting columns or rows adjacent to the reference range. This is only possible if the reference range, where the column or row is inserted, originally spanned at least two cells in the desired direction.
Example: If the range A1:B1 is referenced in a formula and you insert a new column after column B, the reference is expanded to A1:C1. If the range A1:B1 is referenced and a new row is inserted under row 1, the reference is not expanded, since there is only a single cell in the vertical direction.
If you insert rows or columns in the middle of a reference area, the reference is always expanded.

</solution>

I've tried this option and now my inserts are behaving as espected :-)

Hope this helps other people.

P.S.: I think this should be set by default