Copy/Paste From One File to Another

[bart_s]

I found this post without answer.

https://ask.libreoffice.org/en/question/149725/copypaste-from-one-sheet-to-another/?comment=250056#post-id-250056

I am facing exactly the same problem (I think) and I have made it reproducible. Because I do not have an answer, and I think this is indeed an issue, I am creating a new question for it.

Similar to the poster in the link I have financial worksheets, one file per year, one sheet per month Jan, Feb, ... Nov, Dec. Besides those tabs I have a Config sheet. All month sheets refer to the Config sheet for some general settings (thresholds for conditional formats, lookup tables for items with fixed price per year etc.)

The month sheets are setup to be universal. They look at the sheet name to know what month it is. Therefore I must reference data to the Config sheet with the dollar signs like this $Config.$A$1

Now if I copy+paste that reference with dollar signs between the month sheets in one file, everything is fine. When I copy+past those cells to another file, the $Config.$A$1 will become an external link to the original file and becomes something like 'file:///C:/SomDir/SecondFile.ods'#$Config.$A$1 . Removing the leading $ sign is not an option because then the sheet reference will change when you copy+paste between tabs. Copy+Paste Special is no option because you can choose for formula’s and will get the external link or not paste formula’s and you will get values only.

This can easily be tested and reproduced.

Is there any way where I can tick that I do not want external links to be generated when copy+paste between two files ?

edit

I have used OpenOffice before until recently. There are some conditional formats that reference to $Config.$A$2$ as well. In Libreoffice it works, but I cannot find the rules itself anymore in the conditional formatting dialogs

edit2

Now that I found the conditional formatting, I see that the $Config.$A$1 reference is as it should be, without external link. Makes the question even more legit: Why in cell formula’s an external link is created while to the same reference in the conditional format it is not ?

[LeroyG]

Calc automatically transforms formulas, adds links to the same sheet.

I just found that, if the source reference don’t includes $ before the sheet name (i.e., Sheet2.A1), the formula pasted in the new document will not include the 'file:///…'#$ before Sheet2.A1. Tested with LibreOffice 7.2.7.2.

[erAck]

Is there any way where I can tick that I do not want external links to be generated when copy+paste between two files

No, but you can select the Config sheet as well and copy it along together with the other sheet(s).

conditional formatting, I see that the $Config.$A$1 reference is as it should be, without external link

Sounds like a bug to me.

Why in cell formula’s an external link is created

Because if not then you’d get an invalid reference. Cell references with sheet reference refer a sheet position, not a sheet name.

[bart_s]

File 1 and file 2 both have the Month sheets and the Config sheet (sorry if that was not clear from the question). Then I make some nice new functionality in file 1 and want to copy it to file 2. So there is no invalid reference. I also do not copy the entire sheets, only the content or some of the content.

Now also tested in openoffice calc, and there no external link is created. I started checkin on Libreoffice because of the nasty “bad allocation” problems in openoffice, but if my links getting external, then I am not sure which one of the two will cost more work.

[erAck]

You can use Find&Replace (Ctrl+H) to change the external references to the target document’s Config sheet, e.g.

• Find: 'file:///home/user/Documents/sourcefilename.ods'#
• Replace: (empty)

Hit ReplaceAll button.

[Blumenkind]

You are a genious on par with god! ty a lot :’)