Description: 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. E.g.: The sheet that does the import (that I want to use in many places) has a formula like this: ``` =IF(F6>0;'file:///home/nenant/HOST/Dropbox/ekonomi/AB/avtal/Export_GCal_to_GSheet.202106.ods'#$Sheet1.B5;"") ``` When received in the workbook where I want a copy the formula instead ends up like this: ``` =IF(F6>0;;"") ``` 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 7.1.3.2 on Linux (OpenSUSE 15.2) in VMWare Fusion on a Macbook Pro. On the Mac host I have Libreoffice 7.0.3.1 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. Actual Results: External references disappear in the copied sheet Expected Results: All external references should remain intact and still point to the correct cells in the workbook containing the relevant data. Reproducible: Always User Profile Reset: No OpenGL enabled: Yes Additional Info: I can provide the actual sheets to test the defunct behavior. - Version: 7.1.3.2 / 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 Calc: threaded
Actually it does have the same problem on the Mac host as well in Libreoffice 7.0.3.1. 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. To reproduce: 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 > '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. Repro NixOS Version: 7.3.0.0.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 Calc: threaded
Repro with files also in Linux 6.3 and 43all bibisect repos
Since this bug was reported last year a number of updates have happened to Calc. I haven't checked all the variants that I used when I was trying to work around the problem, but one is sort of working now. However, there is still a funny problem remaining. There are 2 files. 'basefile.ods' and 'workfile.ods'. 'basefile.ods' contains the data I want to insert using Sheet > 'Insert Sheet from File...' into 'workfile.ods'. Inserting the sheet 'import.2208' from 'basefile.ods' into 'workfile.ods' now kind of keeps the path and filename (which previously completely disappeared) in the sheet formulas. BUT, part of the external filename is changed! And it differs from time to time how it's changed. When I'm writing this, the filename in all the formulas is changed from 'Export_GCal_to_GSheet.202208.ods' in 'basefile.ods' to 'Export_GCal_to_GSheet.2022084.ods' after it's in 'workfile.ods'. This is very weird. Noting some things though. The filename that it's changed into actually does exist. The existing filenames only differ in the numbers indicating the month. The filename is never changed to something random that is not in the filesystem. So, this seems to indicate that something happens when fetching or checking for the existence of the referred file and picking the wrong one. There are also other '.ods' files in the same directory but the filename never changes to one that is completely different. Only the "date suffix" is changed. I have attached additional example files. - basefile.ods - workfile.ods - Export_GCal_to_GSheet.202208.ods Obviously they need to be placed in the correct path locations and manipulate the existing formulas accordingly to reproduce the behavior.
Created attachment 182179 [details] The file containg data to be filtered in the Calc sheet where the problem is exposed.
Created attachment 182180 [details] The file that contains a "template" to be imported into several other files. This "template" is imported into different similar files in order to filter out data based on names from the exported Google calendar entries in the 'Export_GCal_to_GSheet.202208.ods' worksheet.
Created attachment 182181 [details] The file that inserts the "template" from 'basefile.ods'. The inserted "template" from 'basefile.ods'is is used to filter out data based on names from the exported Google calendar entries in the 'Export_GCal_to_GSheet.202208.ods' worksheet. This information is used in another tab within this Calc worksheet.