Bug 145994 - references to named ranges in external files do not update on document open
Summary: references to named ranges in external files do not update on document open
Status: NEW
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
unspecified
Hardware: All All
: medium enhancement
Assignee: Not Assigned
URL:
Whiteboard:
Keywords:
Depends on:
Blocks: Cell-Name
  Show dependency treegraph
 
Reported: 2021-12-01 18:08 UTC by stderr
Modified: 2024-04-04 19:04 UTC (History)
3 users (show)

See Also:
Crash report or crash signature:


Attachments
file with vlookup commands referencing external data (10.93 KB, application/vnd.oasis.opendocument.spreadsheet)
2021-12-01 18:12 UTC, stderr
Details
file with data referenced by calculations.ods (8.69 KB, application/vnd.oasis.opendocument.spreadsheet)
2021-12-01 18:13 UTC, stderr
Details
source sheet with 4 rows in "Named" (11.34 KB, application/vnd.oasis.opendocument.spreadsheet)
2021-12-02 13:43 UTC, Andreas Säger
Details
target sheet with rows count of external "Named" (11.13 KB, application/vnd.oasis.opendocument.spreadsheet)
2021-12-02 13:43 UTC, Andreas Säger
Details

Note You need to log in before you can comment on or make changes to this bug.
Description stderr 2021-12-01 18:08:40 UTC
Description:
The "Manage Name" menu appears to be accessible only through the drop-down menu with cell number displayed, above the spreadsheet number column, on the left side of the window. When a named range references a named range in another file, the data from that named range does not update on file opening, even if "Allow updating" is selected. References to the named range appear as #NAME? unless the named range is touched by opening the Manage Name window and clicking on the range specification.

Steps to Reproduce:
Create a file, reference_values.ods, which has a table of values:
  A       B
1 fruit	  qty
2 apple   10
3 orange   6
4 banana  42
5 kiwi    12

Name the range by clicking on Manage Names and defining as:

fruit: $sheet1.$a$2:$b$5

Save the file, and create a second file, calculations.ods.

Select Manage Names, and define a new named range:
fruit: ‘file:///c:/users/me/documents/reference_values.ods’#fruit

In the worksheet, create a table

  A       B
1 fruit	  qty
2 apple   =vlookup(a2,fruit,2,0)
3 orange  =vlookup(a3,fruit,2,0)
4 banana  =vlookup(a4,fruit,2,0)
5 kiwi    =vlookup(a5,fruit,2,0)

Save and close both files; close calc.
Open Calc, and load calculations.ods.

When prompted with a warning about automatic updating being disabled, click on the “Allow updating” button.

Hit f9 or ctrl+shift+f9 to force an update; the #NAME? errors remain.

Click on “Manage Names”; select fruit; click on “Range for formula expression”… do not make any changes to the expression; close Manage Names window.
The #NAME? references change from the Actual Results to the Expected Results.

Actual Results:
fruit	qty
apple	#NAME?
orange	#NAME?
banana	#NAME?
kiwi	#NAME?



Expected Results:
fruit	qty
apple	10
orange	6
banana	42
kiwi	12


Reproducible: Always


User Profile Reset: No



Additional Info:
Version: 7.2.3.2 (x64) / LibreOffice Community
Build ID: d166454616c1632304285822f9c83ce2e660fd92
CPU threads: 8; OS: Windows 10.0 Build 19042; UI render: Skia/Vulkan; VCL: win
Locale: en-CA (en_CA); UI: en-US
Calc: CL
Comment 1 stderr 2021-12-01 18:12:57 UTC
Created attachment 176634 [details]
file with vlookup commands referencing external data
Comment 2 stderr 2021-12-01 18:13:36 UTC
Created attachment 176635 [details]
file with data referenced by calculations.ods
Comment 3 Gabor Kelemen (allotropia) 2021-12-02 12:06:54 UTC
Confirming issue in

Version: 7.4.0.0.alpha0+ (x64) / LibreOffice Community
Build ID: b2c56ac95b15b675b9fabed889e7f9709bdc793f
CPU threads: 13; OS: Windows 10.0 Build 19042; UI render: default; VCL: win
Locale: en-US (hu_HU); UI: en-US
Calc: threaded

After downloading the two example files one needs to update the range location in the Manage Names dialog of the calculations.ods to have the functions updated.
After save and reload only clicking in the Manage Names dialogs Range or formula expression field and closing the dialog updates the formula.
The Allow updating button on the infobar does nothing, neither the Recalculate or Recalculate hard commands, or opening & closing the Edit Links dialog.

The behavior seems to be like this since 5.1, in older versions not even this method has worked: so this not a regression.
Comment 4 Andreas Säger 2021-12-02 12:57:42 UTC Comment hidden (obsolete)
Comment 5 Andreas Säger 2021-12-02 13:41:48 UTC
(In reply to Andreas Säger from comment #4)
> It worked like this since OOo1. This is an enhancement request (and a can of
> worms, IMHO).

I think my above statement is wrong. It has been implemented somehow but faulty.
I'll upload a more simple test case with a range named "Named" in the source file and the forumla =ROWS('file:///path/source.ods'#Named) in the target file. 
----
The uploaded row count is 4.
1) Open the source, insert a new row in "Named", save and close.
2) Open the target. The formula reports a row count of zero which is a strange return value. ROWS never returns zero. 
3) Allow updating external references, hit Ctrl+Shift+F9 for hard recalculation. Still zero.
4) Hit F2 <Space> Enter for a dummy edit and the formula returns 4 which is the old result. We would expect 5.
5) Open the source document and repeat step 4). Now the result is correct.
Comment 6 Andreas Säger 2021-12-02 13:43:13 UTC
Created attachment 176654 [details]
source sheet with 4 rows in "Named"
Comment 7 Andreas Säger 2021-12-02 13:43:55 UTC
Created attachment 176655 [details]
target sheet with rows count of external "Named"
Comment 8 Eike Rathke 2021-12-02 13:56:29 UTC
This can be shortened to
=SUM('file:///...'#rangename)
which already results in a #NAME? error when reloading and being hit by the Allow Updating InfoBar.
Comment 9 Joshua Coppersmith 2021-12-02 17:46:41 UTC
This can even be direct entry of a file:/// reference into a cell, as a CSE or as a normal cell value. In any case the #NAME? appears on reload:

=‘file:///c:/users/me/documents/reference_values.ods’#fruit

results in #NAME? on reload, or at least on reload then a proper recalculate.

What is more, if you edit a cell with the file:/// reference then it will update. Even the old add-a-space-then-enter trick works to update. [Adding & T(RAND()*0) to the reference does not seems to help updating, as expected since hard-calculate doesn't help.]

Even more, if you 'tickle' any named range with a file:/// reference not only will 
it update, but so will any cells with direct file:/// references. It does not go the other way around. Editing a direct entry will not cause named range references to update.
Comment 10 Eike Rathke 2023-05-03 23:49:45 UTC
The reason is that for external references the sheet names and used cell data are cached and stored in the target document, but not names nor what names would resolve to (remember it could be cell ranges or any kind of formula expressions that without the source document may not make sense at all).

This kind of deep loading happens only when the formula expression input is resolved, not on reload or recalculation.

For simple named ranges (and defined database ranges) the external names and their ranges would have to be additionally stored in the target document.