Bug 114820

Summary: FILEOPEN XLSX VLOOKUP/HLOOKUP gives Error:504, with a reference to an external xlsx file cells
Product: LibreOffice Reporter: Gabor Kelemen (allotropia) <kelemeng>
Component: CalcAssignee: Bartosz <gang65>
Status: RESOLVED FIXED    
Severity: normal CC: gang65, raal
Priority: medium Keywords: filter:xlsx
Version: 5.4.2.2 release   
Hardware: All   
OS: All   
See Also: https://bugs.documentfoundation.org/show_bug.cgi?id=115162
https://bugs.documentfoundation.org/show_bug.cgi?id=115214
Whiteboard: target:6.1.0 target:6.0.1 target:5.4.6 target:7.4.0
Crash report or crash signature: Regression By:
Attachments: Example file to test the problem
Example file containing VLOOKUP reference to the previous one
Screenshot of the error message
Formula syntax settings used to test the bug
Example file to test the problem - saved from LO 6
Example file containing VLOOKUP reference to the previous one - saved from LO 6
SUMIF and VLOOKUP comparison - SUMIF is working perfectly fine
Screenshot of the LibreOFfice with the fix

Description Gabor Kelemen (allotropia) 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 (allotropia) 2018-01-03 12:04:16 UTC 
Created attachment 138844 [details]
Example file containing VLOOKUP reference to the previous one
Comment 2 Gabor Kelemen (allotropia) 2018-01-03 12:06:15 UTC 
Created attachment 138845 [details]
Screenshot of the error message
Comment 3 Gabor Kelemen (allotropia) 2018-01-03 12:08:44 UTC 
Created attachment 138846 [details]
Formula syntax settings used to test the bug
Comment 4 Gabor Kelemen (allotropia) 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 (allotropia) 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.
Comment 13 Commit Notification 2022-03-24 14:11:13 UTC 
Xisco Fauli committed a patch related to this issue.
It has been pushed to "master":

https://git.libreoffice.org/core/commit/3b30ea9c00598b9de85333b67e8b3339d414eec3

tdf#114820: sc_uicalc: Add unittest

It will be available in 7.4.0.

The patch should be included in the daily builds available at
https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
https://wiki.documentfoundation.org/Testing_Daily_Builds

Affected users are encouraged to test the fix and report feedback.