Bug 144643 - Defined name pointing to external worksheet does not work/cannot be created
Summary: Defined name pointing to external worksheet does not work/cannot be created
Status: UNCONFIRMED
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
Inherited From OOo
Hardware: All All
: medium enhancement
Assignee: Not Assigned
URL:
Whiteboard: QA:needsComment
Keywords:
Depends on:
Blocks: Cell-Reference
  Show dependency treegraph
 
Reported: 2021-09-21 14:10 UTC by NISZ LibreOffice Team
Modified: 2021-10-06 03:49 UTC (History)
2 users (show)

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


Attachments
Target file for which we want to set define name in the other file (9.63 KB, application/vnd.oasis.opendocument.spreadsheet)
2021-09-21 14:10 UTC, NISZ LibreOffice Team
Details
File where the defined name and VLOOKUP function should work (9.59 KB, application/vnd.oasis.opendocument.spreadsheet)
2021-09-21 14:14 UTC, NISZ LibreOffice Team
Details
VLOOKUP works with direct reference to the external document (59.85 KB, image/png)
2021-09-21 14:15 UTC, NISZ LibreOffice Team
Details
VLOOKUP does not work with invalid defined name pointing to external file (70.43 KB, image/png)
2021-09-21 14:15 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 2021-09-21 14:10:15 UTC
Created attachment 175172 [details]
Target file for which we want to set define name in the other file

In the attached file there is a VLOOKUP function in B2, it can use a search range in another file, that works.

However, if I wanted to change the same range in the other file to a defined name in the Manage names window, it would no longer work: the Range selector that I clicked closes when I click through to the other target file, but if I manually type in the Range field the range that VLOOKUP used, it results a non-functioning defined name. This is what is used in cell B3.

The request would be to get the UI to work, to allow the creation of a named range pointing to another file.
Steps to reproduce:
1.	Open attached files in Calc
2.	Open Sheet – Named Ranges and Expressions - Define name in the Reference-External-NamedRange.ods file
3.	Roll up the dialog with the button next to Range or formula expression field, and try to select the A2:A13 range in the other file. The dialog disappears.
4.	If you manually copy the file reference used in the VLOOKUP function in the B2 cell and insert it to the Range or formula expression, and add a name then the named range is created, but it won’t work This is demonstrated in the B3 cell.

Actual results:
Err:510 error in cell B3
The Define name (and Manage Names) dialog does not allow the creation of named ranges pointing to other files.

Expected results:
There should be result from the external worksheet in B3
The Define name (and Manage Names) dialog should allow the creation of named ranges pointing to other files.

LibreOffice details:
Version: 7.3.0.0.alpha0+ (x64) / LibreOffice Community
Build ID: 47a8a65022e3fd7624c95d0341b4809aad11fddb
CPU threads: 8; OS: Windows 10.0 Build 18363; UI render: Skia/Vulkan; VCL: win
Locale: hu-HU (hu_HU); UI: hu-HU
Calc: CL
Comment 1 NISZ LibreOffice Team 2021-09-21 14:14:53 UTC
Created attachment 175173 [details]
File where the defined name and VLOOKUP function should work
Comment 2 NISZ LibreOffice Team 2021-09-21 14:15:24 UTC
Created attachment 175174 [details]
VLOOKUP works with direct reference to the external document
Comment 3 NISZ LibreOffice Team 2021-09-21 14:15:46 UTC
Created attachment 175175 [details]
VLOOKUP does not work with invalid defined name pointing to external file