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
Created attachment 176634 [details] file with vlookup commands referencing external data
Created attachment 176635 [details] file with data referenced by calculations.ods
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.
It worked like this since OOo1. This is an enhancement request (and a can of worms, IMHO).
(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.
Created attachment 176654 [details] source sheet with 4 rows in "Named"
Created attachment 176655 [details] target sheet with rows count of external "Named"
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.
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.
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.