Bug 86978 - some cell formulas not updated after linked files update (need hard recalc)
Summary: some cell formulas not updated after linked files update (need hard recalc)
Status: RESOLVED FIXED
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
4.3.2.2 release
Hardware: Other All
: medium normal
Assignee: Eike Rathke
URL:
Whiteboard: target:4.5.0 target:4.4.0.2 target:4...
Keywords:
Depends on:
Blocks:
 
Reported: 2014-12-03 17:14 UTC by David Woodhouse
Modified: 2015-05-08 19:36 UTC (History)
4 users (show)

See Also:
Crash report or crash signature:


Attachments
offending spreadsheet (416.74 KB, application/vnd.oasis.opendocument.spreadsheet)
2014-12-03 17:14 UTC, David Woodhouse
Details
linked CSV file (2.95 KB, text/csv)
2014-12-03 17:15 UTC, David Woodhouse
Details
Screenshot of error (33.66 KB, image/png)
2014-12-03 17:27 UTC, David Woodhouse
Details
much stripped down test case exposing the behavior (61.44 KB, application/vnd.oasis.opendocument.spreadsheet)
2014-12-16 21:28 UTC, Eike Rathke
Details

Note You need to log in before you can comment on or make changes to this bug.
Description David Woodhouse 2014-12-03 17:14:36 UTC
Created attachment 110415 [details]
offending spreadsheet

Attached is a fairly redacted version of a spreadsheet which tracks my children's savings accounts. It shows that I make an automatic monthly payment into each account, each month.

Fund prices are automatically imported from a linked CSV file, which is automatically generated from fund prices on the bank's web site. Once the prices are available for the first banking day of a given month, the spreadsheet accounts for one more regular payment.

On "Sheet 3", in columns V and W, it tracks the amount paid into each child's account during each tax year. Cells AA3 and AD3 on that sheet show the amount paid into each account during *this* tax year, using the formulae =INDEX($V$1:$V$22,$Y3) and =INDEX($W$1:$W$22,$Y3) respectively.

It is these cells which don't seem to get updated. First load the spreadsheet as-is and try to make sure the link to 'stakeholder.csv' is working. The whole of the second 'CSV_data' sheet is an import of the CSV file, if you need to recreate that.

Observe that the amount paid in to each account this year is £8.

Now, close the spreadsheet and edit the CSV file. Add a final column with a date of something like 2015-01-05. Now reload the spreadsheet. You'll get a prompt saying that "This file contains links to other files. Should they be updated?'. As you click 'yes', watch the 'Payment per tax year' field at the right-hand side of Sheet A.

Watch it *fail* to update the 'YTD 2014/15' from £8 to £9 as the new payment appears. Switch to Sheet 3 and see that although cell V4 contains the updated value, cell AA3 hasn't been updated accordingly.

I don't remember this happening when I first produced this spreadsheet, which was only a few months ago. I'll have been running Fedora 20 with LibreOffice 4.2.6.3 then. I'm on Fedora 21 with 4.3.2.2 now.
Comment 1 David Woodhouse 2014-12-03 17:15:12 UTC
Created attachment 110416 [details]
linked CSV file
Comment 2 David Woodhouse 2014-12-03 17:27:24 UTC
Created attachment 110417 [details]
Screenshot of error

Here's a demonstration of the problem.

Oh, when I say "add a final column" to the CSV file obviously I mean a final row. And it needs to be a row like the others, with two fields. Just repeat the previous price. So add '2015-01-05,165.60' to the end of the file.
Comment 3 David Woodhouse 2014-12-03 22:38:28 UTC
Just confirmed this appears to work fine with 4.2.7.2 on Fedora 20. (And that the failure mode when you try to open a spreadsheet but don't have libreoffice-calc installed is *horrid* and unhelpful).
Comment 4 raal 2014-12-04 08:37:08 UTC
I can reproduce with LO 4.3.4.1, win7

When hard recalc CTRL+SHIFT+F9 is thrown, then cell AA3 have correct value.
Comment 5 Eike Rathke 2014-12-16 21:25:19 UTC
Same issue when not reloading document but using Edit->Links->Update with the changed CSV data, or when manually entering new data in CSV_data.A169:B169
I'm investigating.
Comment 6 Eike Rathke 2014-12-16 21:28:46 UTC
Created attachment 110930 [details]
much stripped down test case exposing the behavior
Comment 7 Eike Rathke 2014-12-17 11:39:12 UTC
It is not related to the INDEX() function, it is a problem in broadcasting and tracking and propagating the changes through the dependencies.
Comment 8 Commit Notification 2014-12-18 12:34:20 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "master":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=573f5dfba805b733dd2da62bde3cd5d9d25879d9

Resolves: fdo#86978 append formula cells to track instead of tree

It will be available in 4.5.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 9 Commit Notification 2014-12-18 12:39:59 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "libreoffice-4-4":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=3c7b69f0e68cfdcc4ec45e6ee1e696b99ae4a780&h=libreoffice-4-4

Resolves: fdo#86978 append formula cells to track instead of tree

It will be available in 4.4.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 10 Eike Rathke 2014-12-18 12:49:13 UTC
Pending review https://gerrit.libreoffice.org/13523 for 4-3
Comment 11 Commit Notification 2015-01-07 13:31:02 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "libreoffice-4-3":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=d210265a1b6c4d7b405ef6e79fd30395ca1bc0aa&h=libreoffice-4-3

Resolves: fdo#86978 append formula cells to track instead of tree

It will be available in 4.3.6.

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 12 Luke 2015-05-07 21:17:36 UTC
David Woodhouse,
Can you please verify that this issue has been fixed for you?
Comment 13 Timur 2015-05-08 07:25:56 UTC
I get formula errors from LO 4.3.7.1. Why?
Comment 14 David Woodhouse 2015-05-08 10:21:02 UTC
(In reply to Luke from comment #12)
> David Woodhouse,
> Can you please verify that this issue has been fixed for you?

Yes, this particular issue appears fixed; thanks. However...

(In reply to Timur from comment #13)
> I get formula errors from LO 4.3.7.1. Why?

The #VALUE! errors in the per-tax-year totals? That started happening in the latest release, and looks like a regression introduced by the fix for bug 42481.

See https://bugs.documentfoundation.org/show_bug.cgi?id=42481#c16 and https://bugzilla.redhat.com/show_bug.cgi?id=1219287
Comment 15 Luke 2015-05-08 19:36:53 UTC
David Woodhouse,
Thanks for clearing that up. Could you please file a report for the regression here on the TDF tracker too?