Updating linked cells using multiple documents

[kmh2001]

When inserting a row in a source Calc document, my referenced document doesn’t update linked cells correctly (in a relative fashion, acts as if links are absolute). Links between the two calc files were created via “Paste Special->Link”)

I have a source Calc file with several rows. I copy data from one row into another Calc file and choose “Paste Special->Link” into the referencing Calc file. If I look at the link in the referencing Calc file it looks like this:

{=‘file:///C:/stuff/source_file.ods’#$Sheet1.B167:Z:167}

If I open source_file.ods and insert a row before row 167 and save the file, I expect the reference file to change that link to:
{=‘file:///C:/stuff/source_file.ods’#$Sheet1.B168:Z:168}

upon “Edit->Links To External Files->Update” but the reference remains with row 167.

I thought this used to work (definitely works in Excel).

Any help would be appreciated.

[Edit - Opaque] Changed title to a short description and added original title to details.

[Lupp]

…I expect the reference file to change that link to…

I’m afraid you expected wrongly. How should the target file know about an inserted row in the source file?
Automatic formula adaptions of this kind I would expect to only work if source and target are in the same document.

[Lupp]

You may need to access the changed range via automatically updating textual addresses in the source file using INDIRECT() or with the help of OFFSET() in a similar way.