Calc- problem linking to data in another spreadsheet.

I have a spreadsheet that links to specific cells in many other spreadsheets by cell names so that if the cell position changes the data will still be valid. With Libreoffice and Windows 7, the formula or link appears rather than the data from the other spreadsheet.

In spreadsheet 1.ods Cell a1=2, Cell a2=3, and Cell a3=sum(a1:a2) and I’ve named it CellA3.

With “Insert from External Data” in spreadsheet 2.ods, the formula, =SUM(A#REF!:A#REF!), from CellA3 appears rather than the data.

Using Hyperlink the link appears and Crtl click takes me to spreadsheet 1.ods rather inserting the data from that cell.

I would do following:

Name area in spreadsheet 1
Open spreadsheet 2
Click at location of the top left corner of the area in spreadsheet 1
Insert > Link to external data (not hyperlink!)
in window URL of external data source click brows buttion (…) and select spreadsheet 1
available areas are listed, thus select
select update time on the bottom area
click OK
area of spreadsheet 1 is visible in spreadsheet 2 and data in these cells can be used for processing in spreadsheet 2

If there is a formula in the cell, Excel returns the value in that cell but Calc returns the formula. The formula copied relates to the 2nd spreadsheet.

Comment 1 is true to a degree, but there are ways to correct this. See my answer. AllanR

It’s not as bad as it seems.

Yes, Excel does resolve any formulae and cell references with respect to the source file, and presents the result (value) to the target file. This is probably the most common requirement when linking cell(s) between spreadsheets and so pleases most.

CALC only works like Excel for source cells which contain text/numerics/self-contained formulae. If the linked cells contain absolute cell addresses, it appears to fail. This is because CALC copies any sheet and cell references (in the source cell) and resolves them within the target file. This is not what most people expect and consider it a fault.

However, it would suit the situation where you want to hold a master formula in just one spreadsheet, but operate on multiple sets of data items in other sheets/files. By changing just one formula, all sheets/files could use the new version.

To achieve more Excel-like results you can:

  1. Use a DDE link from the Target to the Source (enter “=” into target cell, navigate to source cell and Left-click mouse). It’s the easiest to implement but it creates an absolute address to the source cell (e.g. $F$10). Thus if the source cell changes position (e.g. rows inserted above) the wrong data will be reported. Updates at file-open or Ctrl+Shift+F9

  2. Use the Insert > Link to External Data (as in the 1st answer) in the target cell and select the source file, then “Named Area”. This creates a relative link which includes any formatting. Points to note though:

a) The source cell requires to be named before linking.

b) At the time of link set-up, set the update frequency off/on and in seconds, useful where data changes rapidly (note Ctrl+Shift+F9 does not work with this link). This is in addition to the normal refresh at file-open.

c) The source cell should not contain any absolute cell addresses e.g. B1 or $A$3. Instead, use named areas in any formulae in cells you wish to link to. OR …

d) A workaround to avoid having to give names to many absolute cell addresses in the source, is to create a new cell just for linking to (rem to give a name!), and in it simply put “=” so that it points to the “actual cell name” containing the formulae with the absolute addresses. Behind the scenes CALC takes a snapshot of all the source cell-names and their absolute cell addresses and stores them in the target file for subsequent use. These can be observed in the Names Manager (Ctrl+F3) of the target file, with prefix file:/// in the Range column. Note these absolute addresses require updating if any cells are moved in the source sheet. If this happens, simply delete all the file:/// entries in the Names Manager (Ctrl+F3) of the target file, and they are automatically re-created at the next file-open links-update action.

“This is because CALC copies any sheet and cell references (in the source cell) and resolves them within the target file.”
I think this was how OpenOffice works, but I think it is not anymore in LibreOffice.