I thought the answer to this was obvious but it no longer works. I recently upgraded to Libreoffice 6.4.6.2, FreeBSD 11.4, Libreoffice from the FreeBSD Ports. I do not know that the latest upgrade made the difference.
I ruined a spreadsheet by typing numbers and unintentionally erasing formulas. It is way too late for undo. There are two sheets of interest: Entry and Chart. There are many, many cross-sheet links from Chart to Entry (Chart uses data from Entry, and many of these links are now missing) and no links in the other direction (correct). The file is called Project.ods. I retrieve a backup, ProjectBackup.ods, and open both of them. There are no cross-file links in either file, and it should stay that way.
I swear this has worked dozens of times before, but it doesn’t work now. The idea: Copy Chart from ProjectBackup to Project. Select the entire sheet Chart in ProjectBackup, ^C, move cursor to upper-left of Chart in Project, ^V. I get errors in almost every cell, and looking at the formulas, a kind of formula cross-link-file I didn’t know was possible (and maybe it’s not possible - they all get errors). I want this copy done with absolutely no file cross-links. References to Entry from Chart should refer to the version of Entry in the same file as Chart in both files. The backup file I am copying from will be deleted almost immediately, so I don’t want any cross-file links to it.
I get links like =‘file:///home/gordon/oo/ProjectBackup.ods’#$‘Pair Entry’.$E$78 and =MAX(‘file:///home/gordon/oo/ProjectBackup.ods’#$‘Pair Entry’.$C$78:$C$82) .
Why not just USE the backup? There are changes made on Entry that I’d rather not lose. Actually I’d rather just replace a section of Chart but that copy creates cross-file links also.
) to the left of the answer that solves your question.