Bug 137846 - FILEOPEN XLSX Defined name pointing to data table does not resolve source file name
Summary: FILEOPEN XLSX Defined name pointing to data table does not resolve source fil...
Status: NEW
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
4.1 all versions
Hardware: All All
: medium normal
Assignee: Not Assigned
URL:
Whiteboard:
Keywords: filter:xlsx
Depends on:
Blocks: XLSX-DataRange
  Show dependency treegraph
 
Reported: 2020-10-29 08:15 UTC by NISZ LibreOffice Team
Modified: 2021-09-21 14:10 UTC (History)
4 users (show)

See Also:
Crash report or crash signature:
Regression By:


Attachments
Data source file with data table from Excel (9.15 KB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2020-10-29 08:15 UTC, NISZ LibreOffice Team
Details
Example file with defined name referencing the data table and named range in the other file. (9.61 KB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2020-10-29 08:18 UTC, NISZ LibreOffice Team
Details
Screenshot of the original document side by side in Excel and Calc (94.94 KB, image/png)
2020-10-29 08:21 UTC, NISZ LibreOffice Team
Details

Note You need to log in before you can comment on or make changes to this bug.
Description NISZ LibreOffice Team 2020-10-29 08:15:59 UTC
Created attachment 166827 [details]
Data source file with data table from Excel

Attached Excel-made example file contains a data table with month names and a named range with month names.
Other file ha defined names referencing the data table and the named range, then using these in VLOOKUP functions.

When opened in Calc the file name of the data table referencing defined name is not resolved as can be seen in the Manage Names dialog, and the VLOOKUP using them gives Err:501 error.
The simple defined name works fine.

Steps to reproduce:
    1. Open attached files in Calc

Actual results:
Err:501 errors in column C, unresolved file name in the Manage Names dialog.

Expected results:
File name resolved in Manage Names dialog, VLOOKUP works like in Excel.

LibreOffice details:
Version: 7.1.0.0.alpha1+ (x64)
Build ID: f27c4ec5c864395f4cdaec32d7e95ff24e4f43c8
CPU threads: 4; OS: Windows 6.3 Build 9600; UI render: default; VCL: win
Locale: hu-HU (hu_HU); UI: en-US
Calc: CL
Comment 1 NISZ LibreOffice Team 2020-10-29 08:18:32 UTC
Created attachment 166828 [details]
Example file with defined name referencing the data table and named range in the other file.
Comment 2 NISZ LibreOffice Team 2020-10-29 08:21:49 UTC
Created attachment 166829 [details]
Screenshot of the original document side by side in Excel and Calc
Comment 3 Xisco Faulí 2020-11-05 10:46:48 UTC
Reproduced in

Version: 7.1.0.0.alpha1+
Build ID: 9c8ed8c8526b9b696d0bf592eb7d963950f3cef4
CPU threads: 4; OS: Linux 5.7; UI render: default; VCL: gtk3
Locale: en-US (en_US.UTF-8); UI: en-US
Calc: threaded

and

Version: 5.2.0.0.alpha0+
Build ID: 13d4398820ded5914f635757865e258db2db2b57
CPU Threads: 4; OS Version: Linux 5.7; UI Render: default; 
Locale: en-US (en_US.UTF-8)
Comment 4 Xisco Faulí 2020-11-05 10:47:41 UTC
@Eike, this is another name range related issue, I thought you might be interested
Comment 5 Xisco Faulí 2020-11-05 10:48:49 UTC
Also reproduced in

Version 4.1.0.0.alpha0+ (Build ID: efca6f15609322f62a35619619a6d5fe5c9bd5a)
Comment 6 NISZ LibreOffice Team 2021-07-23 08:56:55 UTC
Maybe I found bug 116331 again... but let's keep this open just in case.