Link an external spreadsheet and manage broken paths

To reference a cell in another Calc file, start a formula in the destination and select the source cell in the other open workbook. Keep the source path stable and verify updates explicitly: a displayed number can be a cached result rather than proof that the link still works.

Prepare two files

Use Apache OpenOffice Calc 4.1 desktop with English menus. This documentary exercise uses local synthetic files and has not been executed in a native application. It is about external formula references, not clickable hyperlinks or importing an entire linked sheet.

Create source.ods in a disposable folder. On Sheet1 set A1=Orders, B1=80, A2=Rate, B2=12, A3=Revenue, and B3 with =B1*B2. Save it; B3 should be 960. Create a second workbook, summary.ods, in the same folder. Label A1 Source revenue and A2 Reserve. Save both files before linking so the source has a real path.

Build the reference by selecting the source

1. In summary.ods select B1 and click the equals sign beside the input line to start the formula.
2. Switch to the open source.ods window without abandoning formula entry.
3. Select Sheet1 and click B3.
4. Switch back to summary.ods and click the green checkmark in the input line to finish.
5. In summary B2 enter =B1*0.1. Expected values are 960 and 96.

Inspect B1’s formula. The guide describes an external reference with a file URL, a # separator, the sheet name and cell address, broadly like =‘file:///path/source.ods’#$Sheet1.B3. The actual path syntax varies by operating system. Prefer selection to copying a sample path that does not exist on your computer.

This reference reads a value used by your calculation. It is not a hyperlink that merely opens the source, and it does not copy the complete source workbook into the destination. Opening the summary alone does not make the original source disposable.

Verify a saved change

Change source B1 from 80 to 90 and save source.ods. Its revenue is now 1080. Save and close the files, then reopen the summary. If Calc asks about updating external links, approve only this known local source for the exercise, within your normal security policy.

The expected destination values after updating are B1=1080 and B2=108. If they remain 960 and 96, investigate whether you saved the source and whether the link was updated; do not replace the destination formula with 1080. That would hide the failure by turning a live calculation into a constant.

Reproduce and repair a broken path

Close the documents and rename the disposable source file to source-moved.ods. Reopen the summary and attempt the normal trusted-source update. The old path is now invalid. The guide notes that #REF can indicate such a broken link, but a retained displayed value is not proof of success either.

Open source-moved.ods and rebuild summary B1 with the mouse-selection method above, choosing its Sheet1.B3. Inspect the new formula path. Change the source order count to 100 and save; after the destination updates, expect 1200 and 120. This second changed-input test distinguishes a repaired link from an unchanged cached 1080.

Keep a recoverable structure

Keep a backup of the original summary before repairing real references. Do not point a broken link at a similarly named file without checking its sheet, cell meaning and units. Repairing the path does not prove that the new source is semantically equivalent.

Save, close and reopen both final files and confirm the formula, path and 1200/120 results. If distributing a report without its source, provide an intentionally static export and label it as a snapshot; do not promise the recipient a working live model. Review external links before accepting update prompts from unfamiliar workbooks.

Official reference

Apache OpenOffice 4.1 Calc Guide, Chapter 10, Referencing Other Documents. Recovered guide retrieved 19 October 2026; actual update prompts and persistence remain target-build verification tasks.