<html>
<head>
<base href="https://bugs.documentfoundation.org/">
</head>
<body><table border="1" cellspacing="0" cellpadding="8">
<tr>
<th>Bug ID</th>
<td><a class="bz_bug_link
bz_status_UNCONFIRMED "
title="UNCONFIRMED - VLOOKUP in XLSX with external reference to Windows share saved incorrectly"
href="https://bugs.documentfoundation.org/show_bug.cgi?id=118990">118990</a>
</td>
</tr>
<tr>
<th>Summary</th>
<td>VLOOKUP in XLSX with external reference to Windows share saved incorrectly
</td>
</tr>
<tr>
<th>Product</th>
<td>LibreOffice
</td>
</tr>
<tr>
<th>Version</th>
<td>5.0.0.5 release
</td>
</tr>
<tr>
<th>Hardware</th>
<td>All
</td>
</tr>
<tr>
<th>OS</th>
<td>All
</td>
</tr>
<tr>
<th>Status</th>
<td>UNCONFIRMED
</td>
</tr>
<tr>
<th>Keywords</th>
<td>filter:xlsx
</td>
</tr>
<tr>
<th>Severity</th>
<td>normal
</td>
</tr>
<tr>
<th>Priority</th>
<td>medium
</td>
</tr>
<tr>
<th>Component</th>
<td>Calc
</td>
</tr>
<tr>
<th>Assignee</th>
<td>libreoffice-bugs@lists.freedesktop.org
</td>
</tr>
<tr>
<th>Reporter</th>
<td>baron@caesar.elte.hu
</td>
</tr>
<tr>
<th>Blocks</th>
<td>108897, 109329
</td>
</tr></table>
<p>
<div>
<pre>Created <span class=""><a href="http://bugs.documentfoundation.org/attachment.cgi?id=143815" name="attach_143815" title="Data for vlookup (XLSX)">attachment 143815</a> <a href="http://bugs.documentfoundation.org/attachment.cgi?id=143815&action=edit" title="Data for vlookup (XLSX)">[details]</a></span>
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="<a href="http://schemas.openxmlformats.org/officeDocument/2006/relationships/externalLinkPath">http://schemas.openxmlformats.org/officeDocument/2006/relationships/externalLinkPath</a>"
Target="file:///\\<share>\<path>\lookupsource.xlsx" TargetMode="External"/>
- roundtripped:
<Relationship Id="rId1"
Type="<a href="http://schemas.openxmlformats.org/officeDocument/2006/relationships/externalLinkPath">http://schemas.openxmlformats.org/officeDocument/2006/relationships/externalLinkPath</a>"
Target="/<path>/lookupsource.xlsx" TargetMode="External"/>
The Target attribute is incorrect.
The bug has some resemblance to <a class="bz_bug_link
bz_status_NEW "
title="NEW - XLSX:vlookup formula changed in path to external csv file after saved and re-opened"
href="show_bug.cgi?id=87973">bug 87973</a>
Observed using LO 6.1.0.2 & 5.0.0.5 / Windows 7.</pre>
</div>
</p>
<div id="referenced">
<hr style="border: 1px dashed #969696">
<b>Referenced Bugs:</b>
<ul>
<li>
[<a class="bz_bug_link
bz_status_NEW "
title="NEW - [META] XLSX (OOXML) bug tracker"
href="https://bugs.documentfoundation.org/show_bug.cgi?id=108897">Bug 108897</a>] [META] XLSX (OOXML) bug tracker
</li>
<li>
[<a class="bz_bug_link
bz_status_NEW "
title="NEW - [META] VLookup function bugs and enhancements"
href="https://bugs.documentfoundation.org/show_bug.cgi?id=109329">Bug 109329</a>] [META] VLookup function bugs and enhancements
</li>
</ul>
</div>
<br>
<hr>
<span>You are receiving this mail because:</span>
<ul>
<li>You are the assignee for the bug.</li>
</ul>
</body>
</html>