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