Calc link using cell Name

In Calc, v 6.4.6.2, how do I link to a cell in another ods document using the Name I have assigned instead of the default address. Example, I would like to have the value of a cell that I have named Shares in worksheet A appear in worksheet B using the Name ‘Shares’ rather than the default link to the cell address, i.e. H41. If I use the cell address, the value in the target sheet B is incorrect when I insert or delete cells in worksheet A that change the formula in Shares, unless I also have sheet B open when I do so.
Fund-A.ods
Summary.ods

Please upload your .ods type sample file here.

Hello,

  • Define the name of your cell / range (already done as per question)
  • Save your source sheet
  • Open a new document (Target sheet)
  • Select the target cell
  • Select menu Sheet -> Link to External Data...
  • On the External Data dialog select your source file using Browse button
  • If everything works the name of the Named Range (first step above) appears in pane Available Tables/Ranges
  • Select the name
  • Optional: Select an [x] Update every: frequency
  • Click OK

image description

Note: “[snip]” appears only to hide personal information

Hope that helps.

Thank you for your reply. The Name does not appear in the dialog in the Available Tables/Ranges box. I have the Name defined as a Global name. I have installed Java and LibreOffice shows that I am using Java.

I have tried typing the Name in the formula, instead of the default cell address as
=‘file:///C:/Users/nanmy/Documents/Excel/Finances/Fund-A.ods’#$Sheet1.Shares
and I get #NAME? error.
Windows 10 Professional

I have the Name defined as a Global name

What do you mean by Global name? You defined that (on the source sheet) through Sheet -> Named Ranges and Expressions -> Define, added Shares into the Name: field and kept Scope: being Document (Global)?


> The Name does not appear in the dialog in the Available Tables/Ranges box

Then something is wrong. Either you did not save the source file or there is no permission to open the source or … (Is the storage device of some network/internet resource?).


> 'file:///C:/Users/nanmy/Documents/Excel/Finances/Fund-A.ods'#$Sheet1.Shares

This definitely won’t work

$Sheet1.Shares would be a sheet-local name Shares on sheet Sheet1, those are not supported in external references. If you defined a global name then use it as such, i.e.

='file:///C:/Users/nanmy/Documents/Excel/Finances/Fund-A.ods'#Shares

However, the name appears to be resolved to and remembered as its cell range internally, if the definition of the range changed in the external document then the change will not be reflected in the using document when the link to the external is updated, unless the formula expression is re-entered as new after that. Also, after reloading the using document it results in a #NAME? error. I’d consider both a bug.

Thank you, erAck. Using the formula you provided the link now works. Yes, by global, I meant that I set the Scope to Document (Global) when I defined it in the source sheet. I had tried using a # sign, but see that I need to delete the sheet name in the formula. Thus, as you say, those are not supported in external references. I am able to insert or delete rows in my source document and the target document maintains the link to the named cell which has moved down or up in the column. Works as I would expect!

If I use the Link to External Data dialog box as Opaque suggested, I must use the Browse button to select the file. I had just selected it from the drop down list. When I use the Browse button to select the file, the Shares name does appear in the Available list. However, I get the formula =SUM(B#REF!:B4) inserted using this method which results in #REF! error.

I find it works to use copy/paste special… Link, then edit the formula to replace the sheet.cell reference with Name.