Bug 115244 - FILESAVE XLSX Cell Reference error #REF, with a reference external xlsx file cells
Summary: FILESAVE XLSX Cell Reference error #REF, with a reference external xlsx file ...
Status: RESOLVED DUPLICATE of bug 113541
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
5.4.3.2 release
Hardware: All All
: medium normal
Assignee: Not Assigned
URL:
Whiteboard:
Keywords: filter:xlsx
Depends on:
Blocks: XLSX Files-Linked
  Show dependency treegraph
 
Reported: 2018-01-26 15:25 UTC by Gabor Kelemen (allotropia)
Modified: 2021-09-21 11:31 UTC (History)
3 users (show)

See Also:
Crash report or crash signature:
Regression By:


Attachments
Example data source file made with LO 5.4 (14.00 KB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2018-01-26 15:25 UTC, Gabor Kelemen (allotropia)
Details
Example file with reference to the external data source file (14.13 KB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2018-01-26 15:28 UTC, Gabor Kelemen (allotropia)
Details
The example files in Calc side by side (127.62 KB, image/png)
2018-01-26 15:32 UTC, Gabor Kelemen (allotropia)
Details
Formula syntax settings used to test the bug (177.70 KB, image/png)
2018-01-26 15:32 UTC, Gabor Kelemen (allotropia)
Details
Example file with reference to the external data source file - from Excel13 (8.80 KB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2018-02-13 15:08 UTC, Gabor Kelemen (allotropia)
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Gabor Kelemen (allotropia) 2018-01-26 15:25:03 UTC
Created attachment 139377 [details]
Example data source file made with LO 5.4

These spreadsheets were created with LibreOffice 5.4.3.2 and LibreOffice Dev. 6.0. The main problem here is the cell reference which did not work correctly if we want to refer to an external file. We got an Error (507) message in the cell, if we use the Excel A1 Formula Syntax.

Steps to reproduce:
1. Create a new spreadsheet with LibreOffice Calc 5.4
2. Click on Tools then choose the options.
3. In the Option set the LibreOffice Calc Formula options just like as you can see at the attached Formula_syntax_setting.PNG file.
4. Fill the A1 cells with some simple data.
5. Save the file as “Cell_reference_source.xlsx” or something like that.
6. Create a new spreadsheet with LibreOffice 5.4 and save as “Cell_reference_target.xlsx”. 
7. In the “Cell_reference_target.xlsx” file click on the B1 cell and then click on the “equal sign” or “Function button”. After that click on the “Cell_reference_source.xlsx” file’s A1 cell and hit Enter.
Here is the example code: 
=['file:///c:/users/teszt_admin/desktop/lo-4-hivatkozasok/error-507/cell_reference_source.xlsx']munkalap1!a1


Actual results:
We got an Error:507 code in the “Cell_reference_target.xlsx” file’s B1 cell. If we save the file and reload the spreadsheet the data will be updated and the Error:507 code will disappear. But If we modify the “Cell_reference_source.xlsx” file’s A1 Cell and save it, the data will not updated in the “Cell_reference_target.xlsx” file even if we reload the file. If we click in the function (refresh the link manual) and hit enter we got an Error:507 code again.

Expected results:
We should not receive an error code in the cell, because the link and the syntax is correct. The links should be updated when we got the “This file contains links to another files Should they be updated?” message and we click Yes.


Version: 5.4.3.2
Build ID: 92a7159f7e4af62137622921e809f8546db437e5
CPU threads: 4; OS: Windows 6.1; UI render: default; 
Locale: hu-HU (hu_HU); Calc: group

Version: 6.0.0.1.0+
Build ID: c678dc5309741097d9b0265f03dd279a8794d256
CPU threads: 4; OS: Windows 6.1; UI render: default; 
TinderBox: Win-x86@42, Branch:libreoffice-6-0, Time: 2017-12-28_04:21:33
Locale: hu-HU (hu_HU); Calc: group
Comment 1 Gabor Kelemen (allotropia) 2018-01-26 15:28:18 UTC
Created attachment 139378 [details]
Example file with reference to the external data source file
Comment 2 Gabor Kelemen (allotropia) 2018-01-26 15:32:29 UTC
Created attachment 139379 [details]
The example files in Calc side by side
Comment 3 Gabor Kelemen (allotropia) 2018-01-26 15:32:47 UTC
Created attachment 139380 [details]
Formula syntax settings used to test the bug
Comment 4 Bartosz 2018-02-06 02:05:40 UTC
Confirmed with Ubuntu 16.04 LibreOffice 5.4
Comment 5 Bartosz 2018-02-06 22:19:59 UTC
With latest changes from master, the Error:507 is not appearing anymore. 
Instead the #REF is displayed.
It could be caused by another issue (wrong path).

@Gabor Could you please confirm it with latest Daily Build?
Comment 6 Xavier Van Wijmeersch 2018-02-07 09:26:57 UTC
#ref error and message "The following external file could not be loaded. Data linked from this file did not get updated.

file:///c:/users/teszt_admin/desktop/lo-4-hivatkozasok/error-507/cell_reference_source.xlsx"

Version: 6.1.0.0.alpha0+
Build ID: d30eefb677b446886f7b5bab6de93d489ba63529
CPU threads: 8; OS: Linux 4.14; UI render: default; VCL: kde4; 
TinderBox: Linux-rpm_deb-x86_64@70-TDF, Branch:master, Time: 2018-02-06_16:54:53
Locale: nl-BE (en_US.UTF-8); Calc: grou
Comment 7 Xisco Faulí 2018-02-07 18:55:48 UTC
Putting back to NEW
Comment 8 Gabor Kelemen (allotropia) 2018-02-13 15:08:45 UTC
Created attachment 139869 [details]
Example file with reference to the external data source file - from Excel13

This is just for reference.
Comment 9 QA Administrators 2021-01-09 03:50:12 UTC Comment hidden (obsolete)
Comment 10 NISZ LibreOffice Team 2021-09-21 11:31:14 UTC
I can no longer reproduce this with Calc formula syntax even in:

Version: 6.0.0.3
Build ID: 64a0f66915f38c6217de274f0aa8e15618924765
CPU threads: 4; OS: Windows 10.0; UI render: default; 
Locale: en-US (hu_HU); Calc: CL

When attachment 139377 [details] is opened, a new reference is made to it in a new file and that is saved as xlsx, then reloaded. The reference is correct, updated value in the source file can be retrieved with a recalculation (F9).

In 5.4 it has duplicated the drive letter and I got an error 
"The following external file could not be loaded. Data linked from this file did not get updated.

file:///C:/C:/Users/kelemengabo/Downloads/Cell_reference_source.xlsx".

With Excel formula syntax, it was bad for longer with Err:507 shown in the editor until 6.3:

https://git.libreoffice.org/core/+/6e68110a916500a92a15357b6bf90e62dc631a01

commit 6e68110a916500a92a15357b6bf90e62dc631a01	[log]
author	Eike Rathke <erack@redhat.com>	Thu Jun 13 20:25:49 2019 +0200
committer	Eike Rathke <erack@redhat.com>	Fri Jun 14 02:49:21 2019 +0200

Resolves: tdf#113541 handle external reference in Excel syntax

*** This bug has been marked as a duplicate of bug 113541 ***