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.
Created attachment 110416 [details] linked CSV file
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.
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).
I can reproduce with LO 4.3.4.1, win7 When hard recalc CTRL+SHIFT+F9 is thrown, then cell AA3 have correct value.
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.
Created attachment 110930 [details] much stripped down test case exposing the behavior
It is not related to the INDEX() function, it is a problem in broadcasting and tracking and propagating the changes through the dependencies.
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.
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.
Pending review https://gerrit.libreoffice.org/13523 for 4-3
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.
David Woodhouse, Can you please verify that this issue has been fixed for you?
I get formula errors from LO 4.3.7.1. Why?
(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
David Woodhouse, Thanks for clearing that up. Could you please file a report for the regression here on the TDF tracker too?