Created attachment 108543 [details] simple test ods file For example, if Sheet2.A1 has reference to Sheet1.A1, copying Sheet2 to a new document will cause #REF! error. Steps to reproduce: 1. Open the attached test ods file; 2. Copy sheet 2, "to document: new document" Current behaviour: #REF! error in new document Excepted: should show value "1", or "='/path/to/sheet 1'.A1" (this the way MS Excel behavious) This bug makes it impossible to copy sheet. (99% of the spreadsheet people use have reference between sheets. OS: Win7 X86 Version: 4.3.2.1 Build ID: f9b3ad49d92181b0a1fe7e76f785a2c2cd0847d3 Still the same in vesion 4.3.2.2 P.S.: it seems that in the new document, Calc is trying to reference to the sheet before the copied sheet 2, but faild because that sheet does not exist.
Hi Kevin, thanks for reporting. If there is an absolute reference: 'Sheet 2'.A1: $'Sheet 1'.A1 copying the 'Sheet 2' to a new file the path is added. For me not a bug. If an absolute address the path is added to the address in the new file to retain the referenced address. If a relative address, is needed the referenced sheet in the new file, otherwise we have an error, what is fine for me, so you know something is going wrong. With a relative address, it's possible copy both sheets at time to the new file, selecting the sheets and using Menu/Sheets/Move-copy to do it.
Treating sheet name reference as reletive is a critical design flaw in LibreOffice (and also in the old OpenOffice.org / Apache OpenOffice. Assume the follow: [file1] sheet1 - A1: "1" sheet2 - A2: "=sheet1.A1" In Calc: Copy sheet2 to the end of [file1] (named sheet2_2) --> Current behaviour: sheet2_2 A2 becomes "=Sheet2.A1" Copy sheet2 to a new file [file2] --> Current behaviour: in [file2], sheet2: "=#REF!.A1" But, 99% of the spreadsheet users are expecting it shows exact the same content in sheet2_2 as in sheet2, because we are doing COPY, we are not dragging and filing cells. A "copy" means we duplicate sth and it should remain the same as the original. Treating "=Sheet2.A1" as reletive reference and changing it to reference to another sheet when doing sheet coping/moving is really bug behavior. In MS Excel: Copy sheet2 to the end of [file1] (named sheet2_2) --> Current behaviour: sheet2_2 A2 remains the same formular "=Sheet1.A1" Copy sheet2 to a new file [file2] --> Current behaviour: in [file2], sheet2: "=[file1]Sheet1!A1". Further, we can break the link to [file1] so the cell will show only the value "1". The way MS Excel behavious is excactly what the users want.
I am adding Calc expert Markus Mohrhard to cc list and expecting more advice on this. Thanks in advance.
Relative sheet references are not a flaw, but a feature ;-) It allows greater flexibility to setup references to previous/next sheets and copy sheets and formulas around within the same document. Excel simply does not know relative sheet references at all and treats all sheet references as absolute. However, I agree that when copying a sheet to another document, relative sheet references should be treated the same as absolute, resulting in an external reference to the original document IF the referred sheet is not copied along.
(In reply to Eike Rathke from comment #4) > .... > > However, I agree that when copying a sheet to another document, relative > sheet references should be treated the same as absolute, resulting in an > external reference to the original document IF the referred sheet is not > copied along. But if I'm not wrong then we loose the option for copying relative reference for use in the spreadsheet destination. Maybe an intermediate solution to non break the long standing behaviour for LibreOffice/AOo could be add the option in the move/copy sheets box.
Adjusting only when copying to a NEW document IF the referred sheet is not copied would not break it.
(In reply to Eike Rathke from comment #6) > Adjusting only when copying to a NEW document IF the referred sheet is not > copied would not break it. Forgive me, but maybe it doesn't solve the requested enhancement comment#2 about retain the reference to the source file. I use this feature occasionally. But It would be at least curious, the need to break rules (what I don't like too much, sorry), in what calc seems more advanced. OTHO difficult to know, if it can break how even more people it's using it, if it is working fine for them, then they don't do noise about this matter.
*** Bug 94118 has been marked as a duplicate of this bug. ***
In addition to being out of the standard of existing spreadsheets, which in itself is bad, changing external references from one sheet to another makes no sense and is useless except for some uses more or less esoteric. In years of heavy use of spreadsheets, employment and personal work, there are fundamentally the following situations to copy sheets: 1. To have a new sheet to make some changes or some experimental analysis; 2. To create a new view; 3. To build several (many) sheets with the original as a model. In all these cases the rules should be: 1. External references (references to other sheets) should be kept intact (unchanged); 2. The internal references (references to cells in the sheet being copied) should be changed to the new sheet (the one resulting from the copy). Ex.: Sheet SA being copied to SA_2: =Rec.A11 must be kept as =Rec.A11 =SA.F3:G12 must be changed to =SA_2.F3:G12 =F12 must be kept as =F12 Charts refs. included. *** In summary, the resulting sheet must have the same aspect and behaviour as its original. *** There is plenty of room for improvement, specially with new ideas, but do not alter the basic behaviour established over the years in spreadsheets. Mainly because it is the most intuitive. In case of doubt, try with other spreadsheets programs available, some online. Regards Paulo da Silva
(In reply to Paulo da Silva from comment #9) +1
*** Bug 86652 has been marked as a duplicate of this bug. ***
*** Bug 50860 has been marked as a duplicate of this bug. ***
*** Bug 105364 has been marked as a duplicate of this bug. ***
*** Bug 97044 has been marked as a duplicate of this bug. ***
I'm skeptical about this being bug at all, but I guess it'll make easier for those who don't understand references. This bug is about "copy sheet to a new document" and some duplicates are about "copy sheet before source sheet in the same document", so I change the title to have both situations. But, this is still "negative" title describing problem and for enhancements it should be "positive" and describe solution. So please Kevin change to sth. like "Copying sheet without referred sheet to new file or before referred sheet in same file, relative reference should be treated the same as absolute".
*** Bug 69181 has been marked as a duplicate of this bug. ***
(In reply to Timur from comment #15) #REF! error is just the result, the root cause is the treatment of sheet names as relative reference. It really does not matter where the source sheet is copied to: coping to the beginning, or coping to a new file, they all resulted in the same formula error, the root cause is because libreoffice is treating the cross reference as relative reference. All these duplicates should be either duplicates of bug 85538, or the new enhancement request I have filed as bug 107257.
*** Bug 91211 has been marked as a duplicate of this bug. ***
This is no longer a problem with new ods files creaded with Libreoffice version 5.4, because of the fix to bug 90799. An easy workaround for ods files created with older versions is to manually change the reference to absolute refs. (You can do search and replace to speedup This step). I suggest to close this as NOTABUG (to agree to the ideas that the relative sheet refs is a feature).