https://bugs.documentfoundation.org/show_bug.cgi?id=114820
Bug ID: 114820
Summary: FILESAVE XLSX Vlookup/Hlookup gives Error:504, with a
reference to an external xlsx file cells
Product: LibreOffice
Version: 5.4.2.2 release
Hardware: All
OS: All
Status: UNCONFIRMED
Keywords: filter:xlsx
Severity: normal
Priority: medium
Component: Calc
Assignee: [email protected]
Reporter: [email protected]
Created attachment 138843
--> https://bugs.documentfoundation.org/attachment.cgi?id=138843&action=edit
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
--
You are receiving this mail because:
You are the assignee for the bug._______________________________________________
Libreoffice-bugs mailing list
[email protected]
https://lists.freedesktop.org/mailman/listinfo/libreoffice-bugs
