Download it now!
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: NEW
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
Modified: 2019-01-09 15:37 UTC (History)
3 users (show)

See Also:
Crash report or crash signature:


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
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
Details
The example files in Calc side by side (127.62 KB, image/png)
2018-01-26 15:32 UTC, Gabor Kelemen
Details
Formula syntax settings used to test the bug (177.70 KB, image/png)
2018-01-26 15:32 UTC, Gabor Kelemen
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
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Gabor Kelemen 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 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 2018-01-26 15:32:29 UTC
Created attachment 139379 [details]
The example files in Calc side by side
Comment 3 Gabor Kelemen 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 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.