linked cells. If source changes, target does not get updated

[caughtintheact]

There is probably a simple solution, but I could not find it.
Suppose I have two spreadsheets in separate documents 1.ods and 2.ods.

• List item

I do a paste link of data (in this case the number 973.6547) from cell B3 of spreadsheet 1.ods (ATTACH 1) using copy>paste special>paste special (ATTACH 2) to cell D5 of spreadsheet 2.ods (ATTACH 3) .

• List item

Later, I insert a new row above row 3 of spreadsheet 1, which causes the data in cell D5 to relocate to D6.

The problem is that the target cell, cell D5 of spreadsheet 2 still thinks its source data is in spreadsheet 1 cell B3, not B4. Recalculating by Ctrl+Shift+F9 does not fix this. So after reopening spreadsheet 2, I am asked to update the links (ATTACH 4). You can see that the result was not what it should be, and I would like to learn how to fix it, so that when the source cell changes location it is reflected in the target cell.

The images are in order 1-5

Version: 6.4.7.2 (x64)
Build ID: 639b8ac485750d5696d7590a72ef1b496725cfb5
CPU threads: 8; OS: Windows 10.0 Build 19042; UI render: GL; VCL: win;
Locale: en-US (en_US); UI-Language: en-US
Calc: threaded

[anon73440385]

Later, I insert a new row above row 3 of spreadsheet 1, which causes the data in cell D5 to relocate to D6.

Inserting a row in document 1 does not affect the formula in any cell of document 2 - see your own screenshots, which show you inserted the link to D6 and that it is still in D6 after the insertion of a row before row 3 in document 1.

And of course document 2 does not get any notion of that insert. What you expect would require that Libreoffice (or document 1) has full knowledge about all documents having a link to the current document, where you inserting a row, for sake of opening 'em and modifying existing links.

[caughtintheact]

Actually I did not insert the link to D6. The cell became D6 after I inserted a row above row 5. And when I allowed updating cell D6 changed to 0. I can see now where the screen shots can be misleading.
I do not know the technicalities, but when some time ago I used paste-link in MS Excel, and the links updated when I made changes, but now I have to go through the target sheet at least once a day and fix pasted links if I made any changes. I thought that LibreOffice would have the same capability.

[anon73440385]

???

You wrote Later, I insert a new row above row 3 of spreadsheet 1 You did not tell anything about inserting a row before D5. Finally it is completely unclear why you got an array formula (indicated by curly brackets { and } around your file.. link.

[caughtintheact]

Sorry for any confusion. I am not very technically oriented. My only point is that when I put a row in above another row causing the source cell to change its row number, it no longer linked to the target cell after doing a link update, and I just want to know if there is a way to fix it.

[anon73440385]

put a row in above another row causing the source cell to change its row number, it no longer linked to the target cell

You are asking your question wrong (obviously based on a confusion). If you enter a row in the source file (document), there is no way that the target document gets any information of that row insertion. Hence it cannot adapt the formula, to point to the new moved source cell. There target cell (document) has no idea that anything has changed in the source and this can’t be achieved with MS Excel as well.