Bug 85538 - Copying sheet to new document (or somewhere in same document) causes formula error if cells have relative reference to other sheets
Summary: Copying sheet to new document (or somewhere in same document) causes formula ...
Status: RESOLVED NOTABUG
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
Inherited From OOo
Hardware: All All
: medium enhancement
Assignee: Not Assigned
URL:
Whiteboard:
Keywords:
: 50860 69181 86652 91211 94118 97044 105364 (view as bug list)
Depends on:
Blocks: Cut-Copy Sheet Cell-Reference
  Show dependency treegraph
 
Reported: 2014-10-28 02:26 UTC by Kevin Suo
Modified: 2017-11-06 16:09 UTC (History)
10 users (show)

See Also:
Crash report or crash signature:


Attachments
simple test ods file (10.81 KB, application/vnd.oasis.opendocument.spreadsheet)
2014-10-28 02:26 UTC, Kevin Suo
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Kevin Suo 2014-10-28 02:26:48 UTC
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.
Comment 1 m_a_riosv 2014-10-28 22:10:47 UTC
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.
Comment 2 Kevin Suo 2014-10-30 04:41:14 UTC
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.
Comment 3 Kevin Suo 2014-10-30 04:47:04 UTC
I am adding Calc expert Markus Mohrhard to cc list and expecting more advice on this. 

Thanks in advance.
Comment 4 Eike Rathke 2015-04-23 15:01:24 UTC
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.
Comment 5 m_a_riosv 2015-04-23 22:02:11 UTC
(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.
Comment 6 Eike Rathke 2015-04-27 11:11:14 UTC
Adjusting only when copying to a NEW document IF the referred sheet is not copied would not break it.
Comment 7 m_a_riosv 2015-04-27 21:41:10 UTC
(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.
Comment 8 m_a_riosv 2015-09-12 08:56:29 UTC
*** Bug 94118 has been marked as a duplicate of this bug. ***
Comment 9 Paulo da Silva 2015-09-12 23:17:21 UTC
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
Comment 10 Kevin Suo 2016-01-12 07:58:28 UTC
(In reply to Paulo da Silva from comment #9)
+1
Comment 11 Kevin Suo 2017-04-19 02:29:25 UTC
*** Bug 86652 has been marked as a duplicate of this bug. ***
Comment 12 Kevin Suo 2017-04-19 05:40:13 UTC
*** Bug 50860 has been marked as a duplicate of this bug. ***
Comment 13 Kevin Suo 2017-04-19 05:48:04 UTC
*** Bug 105364 has been marked as a duplicate of this bug. ***
Comment 14 Timur 2017-04-19 08:40:55 UTC
*** Bug 97044 has been marked as a duplicate of this bug. ***
Comment 15 Timur 2017-04-19 09:12:23 UTC
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".
Comment 16 Timur 2017-04-19 09:14:42 UTC
*** Bug 69181 has been marked as a duplicate of this bug. ***
Comment 17 Kevin Suo 2017-04-19 09:54:12 UTC
(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.
Comment 18 Kevin Suo 2017-04-19 09:55:24 UTC
*** Bug 91211 has been marked as a duplicate of this bug. ***
Comment 19 Kevin Suo 2017-11-06 16:09:18 UTC
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).