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
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
Please attach document for testing. Thank you.
Created attachment 127597 [details] booking details
Created attachment 127598 [details] bill having external links
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
Investigating. Cause seems to be the interned string of the reloaded external document not sharing the same pool as the calculating document.
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.
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
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.
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.
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.
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.
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
That would be a different problem. Please submit a separate bug.
(In reply to Eike Rathke from comment #14) > That would be a different problem. Please submit a separate bug. see bug 103773