Referencing named ranges in other files

English
#1

LibreOffice version; os: v7.2.3.2 (x64); windows 10.

I have a file, reference_values.ods, which has several named ranges in it: range1, range2, range3, etc.
I have another file, calculations.ods, which looks up data in the other file: vlookup(“something”,range1,2,0), and so on.

The ranges are named in the first file, reference_values. The second file also has named ranges, but they refer to the names in the former file, so:

reference_values.ods has:
range1 is $sheet1.$a$2:$b$10
range2 is $sheet1.$a$12:$b$24

calculations.ods has:

range1 is ‘file:///c:/users/me/documents/reference_values.ods’#range1
range2 is ‘file:///c:/users/me/documents/reference_values.ods’#range2

When I open calculations.ods, I usually get a warning about automatic updating being disabled, and if I’d like to enable it, click on the “Allow updating” button; I click on the button.

After this, I’m staring at calculations.ods in calc, all my data is there, any cell with a vlookup() says #NAME? instead of the looked up value. If I hit f9, or ctrl+shift+f9 to force an update, the #NAME? error remains.

If I click on “manage names” and get that window open, select range1, then click on “Range for formula expression”… just click on it, no changes or edits, and repeat this for range2, all of my calculations begin to work again.

I’d love to figure out how to make it work without manually opening the named ranges editor each time I open the calculations file. How do I get this to just work? Am I doing something wrong or missing a step?

#3

Villeroy’s solution works, but doesn’t address the question about these “file:///…#…” links not listening to the update request when loading the document. The error stderr is reporting directly affects target referencing these “file:///…#…” links, not just targeting through named ranges. Does LO Calc still support target referencing like this? If so, I’d say this is a bug, or at least an oversight that clicking Allow updating on the yellow message bar doesn’t update these references along with DDE() and Sheet-Link… links.

Without using a range name you can create an array formula by just entering the ‘file:///c:/users/me/documents/reference_values.ods’#range1 as a CSE. But the #NAME? will still appear upon reload. If you then Ctrl+/,F2,Space,Ctrl+Shift+Enter on that CSE the data will load. In fact, if you have both a named range and a direct-entered link like this, both will be updated if you go to the named range manager and “tickle” the named range.

I think that if LO is to support these file:///…#-style links, it is worth finding out why Allow updating isn’t “hitting the lever” to update these links.

1 Like
#5

file:/// links keep a hidden data copy of the referenced files. You can pass the target document without the source document to another machine and the link will work as long as you don’t try to update the link. The other types of links open the source document and evaluate the named reference.

#6

Then in your opinion does that make it a bug that these do not get updated after confirming Allow updating? Or is it practically infeasible?

#2
  1. menu;Sheet>Insert Sheet From File… with “Link” option imports a linked copy of a sheet including the names.
  2. menu:Sheet>Link to external data… inserts a linked copy of a named range
  3. Open source and target document side by side. Drag the source range to the target and hold Ctrl+Shift when dropping. This creates a DDE array formula. You can replace the last argument (range address) with the range name.
Comment lier dynamiquement plusieurs cellules d'un classeur à une plage nommée d'un autre classeur?
#4

Sounds simply like a bug. Please submit at https://bugs.documentfoundation.org/ and report the resulting bug number back here. Thanks.

1 Like
#7

It is not a bug. Since decades is implemented like this. It was never intended that file: links resolve names in other documents.

#8

“Since decades is implemented like this.” does not bear on whether it’s a bug.

If this is not an acceptable way of linking to a named range, why does Calc accept and correctly resolve names in file:/// links? Calc should not permit entering a named range in another file if it can’t handle it; instead, it handles it when typing in the range, but not on subsequent reopening of the file. All or none. Either permit this method and consistently resolve the external name, or do not permit this method.

That sounds harsh and critical, but is just me advocating for what I think is a very elegant way of linking data; so, I’ll be in the corner humbly requesting a fix, crossing my fingers that the amazing team of programmers for this project take up the task. Bug report filed.

#9

Developers call it a bug if it is broken. This is not broken. It works as intended. What you are requesting is not a bug fix. This would be an request for enhancement. As far as I know, DDE and “link to external data” work properly with external names.
Did you post this recent bug report? https://bugs.documentfoundation.org/show_bug.cgi?id=145994

EDIT: I’m all wrong. I did not notice that this enhancement has been implemented in a faulty manner.

1 Like
#10

Yes it is. =SUM('file:///...'#rangename) as external name does work, but already that results in a #NAME? error when reloading and being hit by the Allow Updating InfoBar.

#11

Yes I did. At the end of my last post, you’ll see “Bug report filed.” I suppose I could have included the link to it; oops.

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.