I created a sheet that fetches data from a different workbook. The sheet does some manipulation and is made flexible so that it is of use in other workbooks when copied into those by changing certain text. However the file references disappear when copied (or moved) using the sheet's tab functionality.
Trying from the other end to use 'Sheet > Insert Sheet from File...' yields the same bad result. Same thing when using 'Sheet > Insert Sheet...' and choosing the file option.
At one point the file references did not completely disappear, but instead they were all changed into the file path from which I inserted the relevant sheet rather than the original file path to where the information actually is located. But it only happened once and I have not been able to get that behavior again. Mostly the references just disappear altogether.
The sheet that does the import (that I want to use in many places) has a formula like this:
When received in the workbook where I want a copy the formula instead ends up like this:
Except that one time when instead the file reference had been changed into:
=IF(F6>0;'file:///home/nenant/HOST/Dropbox/ekonomi/AB/avtal/kunder/ASIH-Vallentuna/Fakturaunderlag 2021.ASIH Vallentuna.ods'#$Sheet1.B5;"")
Which is completely wrong.
The import sheet that is supposed to be reused in other workbooks has MANY external references, ~ 240 (if that has anything to do with it?)
I am running Libreoffice 188.8.131.52 on Linux (OpenSUSE 15.2) in VMWare Fusion on a Macbook Pro.
On the Mac host I have Libreoffice 184.108.40.206 and the same set of files do not exhibit this problem.
Steps to Reproduce:
1. Create a sheet with (possibly many) external cell references to a different workbook.
2. Use tab menu to copy the sheet to another workbook, OR...
3. From a third workbook use 'Sheet > Insert Sheet from File...' to copy the the sheet with all the external references.
External references disappear in the copied sheet
All external references should remain intact and still point to the correct cells in the workbook containing the relevant data.
User Profile Reset: No
OpenGL enabled: Yes
I can provide the actual sheets to test the defunct behavior.
Version: 220.127.116.11 / LibreOffice Community
Build ID: 10(Build:2)
CPU threads: 5; OS: Linux 5.3; UI render: default; VCL: kf5
Locale: sv-SE (en_US.UTF-8); UI: en-US
Actually it does have the same problem on the Mac host as well in Libreoffice 18.104.22.168.
So it's intemittent and difficult to reproduce every time. But it does occur most of time. Right now e.g. I am not able to copy that sheet with all the necessary references into a different workbook, 240 of them.
Created attachment 173426 [details]
The file that contains input data
This is the file from where input data is fetched for further manipulation.
Created attachment 173427 [details]
A workbook that contains a sheet that imports the data from the input file
This a one sheet workbook that only contains a sheet that imports the data from the input file. Normally it would be part of workbook with more sheets.
Created attachment 173428 [details]
A workbook where I want the import sheet copied into
This is one of the workbooks where I want the import sheet copied into complete with all formulas unaltered and with references intact.
I now uploaded sample worksheets to demonstrate the bug.
1. Open the 'importer.q.ods' and make sure it is showing data from the file 'Export_GCal_to_GSheet.202106.redacted.ods'.
2. Open 'Tidsredovisning 2021.Camilla Norén.ods'. This is where I want a copy of the importer sheet.
3. In 'importer.q.ods', right click the sheet tab and choose 'Move or Copy Sheet... '. In the dialog choose to copy the sheet to the other document 'Tidsredovisning 2021.Camilla Norén.ods'.
4. In 'Tidsredovisning 2021.Camilla Norén.ods' look at the formulas in columns A, B, C and S (as well as G, I, K and M). The reference to workbook 'Export_GCal_to_GSheet.202106.redacted.ods' is gone everywhere.
(In reply to Nenad Antic from comment #5)
> I now uploaded sample worksheets to demonstrate the bug.
> To reproduce:
> 1. Open the 'importer.q.ods' and make sure it is showing data from the file
> 2. Open 'Tidsredovisning 2021.Camilla Norén.ods'. This is where I want a
> copy of the importer sheet.
> 3. In 'importer.q.ods', right click the sheet tab and choose 'Move or Copy
> Sheet... '. In the dialog choose to copy the sheet to the other document
> 'Tidsredovisning 2021.Camilla Norén.ods'.
> 4. In 'Tidsredovisning 2021.Camilla Norén.ods' look at the formulas in
> columns A, B, C and S (as well as G, I, K and M). The reference to workbook
> 'Export_GCal_to_GSheet.202106.redacted.ods' is gone everywhere.
Version: 22.214.171.124.alpha0+ / LibreOffice Community
Build ID: b1df9c67349cf4cc5be4128d797aefb87f50e38f
CPU threads: 16; OS: Linux 5.13; UI render: default; VCL: x11
Locale: fi-FI (fi_FI.UTF-8); UI: en-US
Repro with files also in Linux 6.3 and 43all bibisect repos