https://bugs.documentfoundation.org/show_bug.cgi?id=118990
Bug ID: 118990
Summary: VLOOKUP in XLSX with external reference to Windows
share saved incorrectly
Product: LibreOffice
Version: 5.0.0.5 release
Hardware: All
OS: All
Status: UNCONFIRMED
Keywords: filter:xlsx
Severity: normal
Priority: medium
Component: Calc
Assignee: [email protected]
Reporter: [email protected]
Blocks: 108897, 109329
Created attachment 143815
--> https://bugs.documentfoundation.org/attachment.cgi?id=143815&action=edit
Data for vlookup (XLSX)
Prerequisite steps (Windows share and Excel needed):
- Put the attached data source spreadsheet into a shared folder.
- In Excel, in a new spreadsheet, enter:
- in A1: =VLOOKUP(B1,'\\<share>\<path>\[lookupsource.xlsx]Sheet1'!A1:B5,2)
- in B1: C
(ie. in the shared spreadsheet, in A1:B5 range try to look up the
corresponding value for the row containing "C")
- Save the file.
- Open file in Calc, verify that A1 contains the correct lookup result (you
might have to allow loading external data), then save and reopen it.
For reference note that when opening the original file, the formula looks like
this in Calc (the "good" version):
=VLOOKUP(B1;'file://<share>/<path>/lookupsource.xlsx'#$Sheet1.A1:B5;2)
=> The location in the VLOOKUP formula is changed to something like:
<drive>/<path>/lookupsource.xlsx
When unzipping the files, in 'xl\externalLinks\_rels\externaLink1.xml.rels' has
a significant difference between the original, and the one roundtripped in
Calc:
- original:
<Relationship Id="rId1"
Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/externalLinkPath";
Target="file:///\\<share>\<path>\lookupsource.xlsx" TargetMode="External"/>
- roundtripped:
<Relationship Id="rId1"
Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/externalLinkPath";
Target="/<path>/lookupsource.xlsx" TargetMode="External"/>
The Target attribute is incorrect.
The bug has some resemblance to bug 87973
Observed using LO 6.1.0.2 & 5.0.0.5 / Windows 7.
Referenced Bugs:
https://bugs.documentfoundation.org/show_bug.cgi?id=108897
[Bug 108897] [META] XLSX (OOXML) bug tracker
https://bugs.documentfoundation.org/show_bug.cgi?id=109329
[Bug 109329] [META] VLookup function bugs and enhancements
--
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
