Bug 114820 - FILEOPEN XLSX VLOOKUP/HLOOKUP gives Error:504, with a reference to an external xlsx file cells
Summary: FILEOPEN XLSX VLOOKUP/HLOOKUP gives Error:504, with a reference to an externa...
Status: RESOLVED FIXED
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
5.4.2.2 release
Hardware: All All
: medium normal
Assignee: Bartosz
URL:
Whiteboard: target:6.1.0 target:6.0.1 target:5.4.6
Keywords: filter:xlsx
Depends on:
Blocks:
 
Reported: 2018-01-03 12:02 UTC by Gabor Kelemen
Modified: 2018-02-06 21:28 UTC (History)
2 users (show)

See Also:
Crash report or crash signature:


Attachments
Example file to test the problem (14.11 KB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2018-01-03 12:02 UTC, Gabor Kelemen
Details
Example file containing VLOOKUP reference to the previous one (14.99 KB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2018-01-03 12:04 UTC, Gabor Kelemen
Details
Screenshot of the error message (227.46 KB, image/png)
2018-01-03 12:06 UTC, Gabor Kelemen
Details
Formula syntax settings used to test the bug (173.62 KB, image/png)
2018-01-03 12:08 UTC, Gabor Kelemen
Details
Example file to test the problem - saved from LO 6 (14.11 KB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2018-01-03 14:11 UTC, Gabor Kelemen
Details
Example file containing VLOOKUP reference to the previous one - saved from LO 6 (15.00 KB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2018-01-03 14:12 UTC, Gabor Kelemen
Details
SUMIF and VLOOKUP comparison - SUMIF is working perfectly fine (5.95 KB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2018-02-04 00:36 UTC, Bartosz
Details
Screenshot of the LibreOFfice with the fix (55.50 KB, image/png)
2018-02-05 23:54 UTC, Bartosz
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Gabor Kelemen 2018-01-03 12:02:45 UTC
Created attachment 138843 [details]
Example file to test the problem

These spreadsheets were created with LibreOffice 5.4.3.2 and LibreOffice Dev. 6.0. The main problem here is the VLOOKUP function doesn't work correctly if we refer to an external XLS/XLSX file, and we get an Error (504) message in the cell. If we use the VLOOKUP function in the local (source.xlsx) file, it works perfectly.

Steps to reproduce:
1. Create a new spreadsheet with LibreOffice 5.4
2. Click on Tools then choose Options.
3. In the Options set the LibreOffice Calc Formula options just like as you can see at the attached Formula_syntax_setting.PNG file. I use these settings because this is the only one which is interoperable with Microsoft Excel.
4. Fill at least two columns with some simple data.
5. Save the file as “Source.xlsx”. This will be the source file which contains the queried data.
6. Create a new spreadsheet with LibreOffice 5.4 and save as VLOOKUP_Target_First_Save.xlsx. (This will be the reference file.
7. In the VLOOKUP_Target_First_Save.xlsx file create a correct VLOOKUP function which refers to the “Source file” data.
Here is the example code: 
VLOOKUP('file:///C:/Users/teszt_admin/Desktop/VLOOKUP_BUG/Source.xlsx'#$Sheet1.A1;'file:///C:/Users/teszt_admin/Desktop/VLOOKUP_BUG/Source.xlsx'#$Sheet1.A1:B3;2;0)
8. Click Ok in the Function Wizard and reload the file.

Actual results:
We get an Error (504) message in the cell for these two functions. Other functions work well if we use these steps, but the “Update links when opening” feature doesn't work, so we have to update the links manually.

Expected results:
We shouldn't receive an error code in the cell, because the function is correct and works perfectly in the local files. The Links should be updated when we answer “Yes” in the “This file contains links to another files Should they be updated?” dialog.

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.0.beta1+
Build ID: 29228e83df009cf76ac819ed024527be1092f065
CPU threads: 4; OS: Windows 6.1; UI render: default; 
TinderBox: Win-x86@42, Branch:libreoffice-6-0, Time: 2017-12-04_23:15:34
Locale: hu-HU (hu_HU); Calc: group threaded
Comment 1 Gabor Kelemen 2018-01-03 12:04:16 UTC
Created attachment 138844 [details]
Example file containing VLOOKUP reference to the previous one
Comment 2 Gabor Kelemen 2018-01-03 12:06:15 UTC
Created attachment 138845 [details]
Screenshot of the error message
Comment 3 Gabor Kelemen 2018-01-03 12:08:44 UTC
Created attachment 138846 [details]
Formula syntax settings used to test the bug
Comment 4 Gabor Kelemen 2018-01-03 14:11:38 UTC
Created attachment 138852 [details]
Example file to test the problem - saved from LO 6
Comment 5 Gabor Kelemen 2018-01-03 14:12:03 UTC
Created attachment 138853 [details]
Example file containing VLOOKUP reference to the previous one - saved from LO 6
Comment 6 Xavier Van Wijmeersch 2018-01-03 16:19:30 UTC
I confirm with

Version: 5.3.8.0.0+
Build ID: 7f1297d9b4f449eb9ada8008fb21b7046d1a8f19
CPU Threads: 8; OS Version: Linux 4.9; UI Render: default; VCL: kde4; Layout Engine: new; 
TinderBox: Linux-rpm_deb-x86_64@70-TDF, Branch:libreoffice-5-3, Time: 2017-11-10_15:56:34
Locale: nl-BE (en_US.UTF-8); Calc: group

Version: 5.4.4.2
Build ID: 2524958677847fb3bb44820e40380acbe820f960
CPU threads: 8; OS: Linux 4.9; UI render: default; VCL: kde4; 
Locale: nl-BE (en_US.UTF-8); Calc: group

Version: 6.1.0.0.alpha0+
Build ID: 15d9087ff1e495e234b2cf073329c3358abb4dc0
CPU threads: 8; OS: Linux 4.9; UI render: default; VCL: kde4; 
Locale: nl-BE (en_US.UTF-8); Calc: group threaded
Comment 7 raal 2018-01-05 08:40:03 UTC
confirmed in comment 6
Comment 8 Bartosz 2018-02-04 00:36:25 UTC
Created attachment 139563 [details]
SUMIF and VLOOKUP comparison - SUMIF is working perfectly fine
Comment 9 Bartosz 2018-02-05 23:54:10 UTC
Created attachment 139616 [details]
Screenshot of the LibreOFfice with the fix

With big help of Eike, I managed to fix that issue.
Review:
https://gerrit.libreoffice.org/#/c/49263/

In attachment you could find the screenshot from LO after fix.
Comment 10 Commit Notification 2018-02-06 17:52:56 UTC
Bartosz Kosiorek committed a patch related to this issue.
It has been pushed to "master":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=5c3d0a70a0edb182714725ef920d74667feffb0e

tdf#114820 Fix reading external reference for VLOOKUP and HLOOKUP

It will be available in 6.1.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 11 Commit Notification 2018-02-06 19:18:41 UTC
Bartosz Kosiorek committed a patch related to this issue.
It has been pushed to "libreoffice-6-0":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=b8e9f185c509213b4daae018ec27483dacdad2a0&h=libreoffice-6-0

tdf#114820 Fix reading external reference for VLOOKUP and HLOOKUP

It will be available in 6.0.1.

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 2018-02-06 19:59:36 UTC
Bartosz Kosiorek committed a patch related to this issue.
It has been pushed to "libreoffice-5-4":

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

tdf#114820 Fix reading external reference for VLOOKUP and HLOOKUP

It will be available in 5.4.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.