Ask Your Question
0

Calc link using cell Name

asked 2020-10-16 11:12:08 +0200

nansnow gravatar image

updated 2020-10-17 10:34:34 +0200

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. C:\fakepath\Fund-A.ods C:\fakepath\Summary.ods

edit retag flag offensive close merge delete

Comments

Please upload your .ods type sample file here.

Zizi64 gravatar imageZizi64 ( 2020-10-16 11:33:50 +0200 )edit

1 Answer

Sort by » oldest newest most voted
0

answered 2020-10-16 12:10:59 +0200

Opaque gravatar image

updated 2020-10-16 12:11:48 +0200

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.

edit flag offensive delete link more

Comments

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

nansnow gravatar imagenansnow ( 2020-10-17 10:28:31 +0200 )edit

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

Opaque gravatar imageOpaque ( 2020-10-17 10:44:35 +0200 )edit

$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.

erAck gravatar imageerAck ( 2020-10-17 13:36:57 +0200 )edit

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 ...(plus)

nansnow gravatar imagenansnow ( 2020-10-17 17:36:20 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2020-10-16 11:12:08 +0200

Seen: 23 times

Last updated: Oct 17