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.
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.
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.
Version: 126.96.36.199.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
Created attachment 175173 [details]
File where the defined name and VLOOKUP function should work
Created attachment 175174 [details]
VLOOKUP works with direct reference to the external document
Created attachment 175175 [details]
VLOOKUP does not work with invalid defined name pointing to external file