Download it now!
Bug 101691 - Links to external cells broken
Summary: Links to external cells broken
Status: RESOLVED FIXED
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
4.3 all versions
Hardware: All All
: medium normal
Assignee: Eike Rathke
URL:
Whiteboard: target:5.3.0 target:5.2.4 target:5.1....
Keywords: regression
Depends on:
Blocks:
 
Reported: 2016-08-24 06:29 UTC by Eric
Modified: 2016-11-08 10:59 UTC (History)
2 users (show)

See Also:
Crash report or crash signature:


Attachments
booking details (83.57 KB, application/vnd.oasis.opendocument.spreadsheet)
2016-09-24 07:08 UTC, Eric
Details
bill having external links (43.22 KB, application/vnd.oasis.opendocument.spreadsheet)
2016-09-24 07:09 UTC, Eric
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Eric 2016-08-24 06:29:59 UTC
Hi

for years I have used a set of calc-sheets with references in formulas to each other. Syntax: =['file:///C:/path/to/file 2016.ods']Projektplanung!D4

Extras.Options.LibreOffice Calc.Formula.Formel-Einstellungen="Excel A1"
Extras.Options.LibreOffice Calc.Formula.Detailed setup for computation="Defaults/Vorgaben/First option"

Before upgrading to version 5 of LO this worked fine in all versions I have been using up to 4.4.7.2. After upgrading to 5.1.5.2 RC behavior was like this:
- after loading a version 4-file without updating formulas the values were OK
- after recomputing formulas (F9) err:507 where displayed all over
- copying an old formula to a new cell: err:507
- playing with Extras.Options.LibreOffice Calc.Formula.Formel Einstellungen only helped sometimes(!?)
- resetting or deleting the user profile didn't help
- reformating all formulas in all files to a new #$.-format is not an option really

As this is an important bug to me I have tried with 4 different versions of LO5 with no difference in the result: all were wrong. Version 4.4.7.2 is the last version which can handle external sheet links correctly.

As I found I am not alone with this bug (see Bug 97185).

btw: I found it very inconveniant that with each new installation of LO an existing installation was removed without me being asked before. LO accepts parallel installations of different versions. This is essential in the process of upgrading to test code quality against features used. What's about an option flag during installation?

Regards, Eric
Comment 1 Eric 2016-08-24 10:25:40 UTC
EDIT:

After having tested again I recognize that I have to go back to where I came from: LO 4.3.7.2 to have working external cell references.

btw: I found parallel installations are done thru administrative installations
Comment 2 raal 2016-09-23 14:32:44 UTC
Please attach document for testing. Thank you.
Comment 3 Eric 2016-09-24 07:08:52 UTC
Created attachment 127597 [details]
booking details
Comment 4 Eric 2016-09-24 07:09:47 UTC
Created attachment 127598 [details]
bill having external links
Comment 5 Eric 2016-09-24 07:10:23 UTC
Hi raal

attached you will find 2 test-files (saved with LO 4.3.7.2):
- ZE 2016 MOM.ods with detailed project working times for each month of a year
- PP JT 2016.ods importing and aggregating the former monthly details to a bill for each project customer

The second file uses external links to the first file in the formulas, e.g.
['file:///C:/Users/Jive/Arbeit/Projekte/Projektmanagement/4LO/ZE 2016 MOM.ods']Aug!$I4

Settings in both LO versions:
Extras.Options.LibreOffice Calc.Formula.Formel-Syntax="Excel A1"
Extras.Options.LibreOffice Calc.Formula.Detailed setup for computation="Defaults/Vorgaben/First option" activated

In LO 4.3.7.2 sheet "P1" in file "PP JT 2016.ods" is correctly filled with values in the rows 3-9, 11-17 and 19-23, e.g. column B having all cells set to "2,00" and cells in column C set to "MA".

In LO 5.1.5.2 the whole sheet is empty (with exception to the "x" in A23). This is wrong. Playing around with Extras.Options.LibreOffice Calc.Formula.Detailed setup for computation=OpenCL-option and its modal window does not change results.

I just installed LO5 without changing any of the setup.

Thanks for your review.

Regards Eric
Comment 6 Eike Rathke 2016-10-07 20:26:22 UTC
Investigating. Cause seems to be the interned string of the reloaded external document not sharing the same pool as the calculating document.
Comment 7 Commit Notification 2016-10-11 13:26:37 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "master":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=351164ea2c70923e47d5f01f3934c73cfc5839a9

Resolves: tdf#101691 re-intern strings of external formula results

It will be available in 5.3.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 8 Eike Rathke 2016-10-11 13:33:25 UTC
Pending review
https://gerrit.libreoffice.org/29694 for 5-2
https://gerrit.libreoffice.org/29695 for 5-1
https://gerrit.libreoffice.org/29696 for 5-1-6
Comment 9 Commit Notification 2016-10-13 05:22:22 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=8a0ec842b92857b6950c95e2c00ffa6b0ec12082&h=libreoffice-5-2

Resolves: tdf#101691 re-intern strings of external formula results

It will be available in 5.2.4.

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 Commit Notification 2016-10-13 05:22:29 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "libreoffice-5-1":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=dfcf0bbcc51f50dfa93ac74623a069c3b527527a&h=libreoffice-5-1

Resolves: tdf#101691 re-intern strings of external formula results

It will be available in 5.1.7.

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 11 Commit Notification 2016-10-18 06:31:05 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "libreoffice-5-1-6":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=f200b58bd47ca25f7e157c50b36fff4cf4162b70&h=libreoffice-5-1-6

Resolves: tdf#101691 re-intern strings of external formula results

It will be available in 5.1.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 Commit Notification 2016-10-25 10:25:27 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "libreoffice-5-2-3":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=24cf67d7df78c95011cb7f56c6673b45fffb7bda&h=libreoffice-5-2-3

Resolves: tdf#101691 re-intern strings of external formula results

It will be available in 5.2.3.

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 13 Eric 2016-11-07 14:58:18 UTC
Hi,

back again for testing.

I installed LO 5.2.3.3
I loaded same old .ods-files and everything was fine - thank you!
Copy&paste of external links within a file was OK.

But: 
When I copy&paste intact external links from another (external) file the cell-content receives:
=WENN(<>"";;"")

The original content was:
=WENN('file:///C:/path/to/file.xls'#$März.F17<>"";'file:///C:/path/to/file.xls'#$März.B17;"")

Same result with a path without german umlaute.

If I paste it to a writer-file, I see a calc-cell with an error inside the cell. If I paste it without format, I see the result data entry of the original cell, e.g. "07. November 2016".
If I paste it to the Microsoft editor notepad.exe I see the date as well.

Could you, please, check why pasting a cell-content looses all external paths.

Thanks again.

Best regards, Eric
Comment 14 Eike Rathke 2016-11-07 16:16:54 UTC
That would be a different problem. Please submit a separate bug.
Comment 15 Cor Nouws 2016-11-08 10:59:16 UTC
(In reply to Eike Rathke from comment #14)
> That would be a different problem. Please submit a separate bug.

see bug 103773