Bug 45385 - EDITING: Copy Paste formula to different document adds source document filename to references
Summary: EDITING: Copy Paste formula to different document adds source document filena...
Status: RESOLVED NOTABUG
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
3.5.0 RC2
Hardware: Other Windows (All)
: medium normal
Assignee: Not Assigned
URL:
Whiteboard:
Keywords: regression
Depends on:
Blocks:
 
Reported: 2012-01-30 01:26 UTC by Rainer Bielefeld Retired
Modified: 2014-04-30 05:34 UTC (History)
4 users (show)

See Also:
Crash report or crash signature:


Attachments
Sample Document (25.40 KB, application/vnd.oasis.opendocument.spreadsheet-template)
2012-01-30 01:26 UTC, Rainer Bielefeld Retired
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Rainer Bielefeld Retired 2012-01-30 01:26:41 UTC
Created attachment 56314 [details]
Sample Document

Steps how to reproduce with "LibreOffice 3.5.0 RC2 English UI/Locale [Build-ID: e371a95-bf68a13-5a1aa2b-d3c1ae9-b938258] on German WIN7 Home Premium (64bit):

1. open attached "source.ots"
   > file with name 'Untitled1.ods' or similar opens
2. go to Sheet "Lieferschein", click  C12 if necessary
3. <control+c> for copy
4. Menu 'File -> New -> Spreadsheet'
   > New spreadsheet  'Untitled1.ods' or similar opens
5. In newspreadsheet click Shett3.C12
6. <control+v>
   Expected: formula "=IF($Tabelle1.C12<> ..."
   Actual: formula   "=IF('Untitled1'#$Tabelle1.C12<> ..."

Copy + Paste has added document name of source document to reference

This prolbem is not reproducible with a very simple new source document and a formula "=Sheet1.A1" on Sheet3

Works fine in 3.4.5, so REGRESSION

Was ok in Master 2011-07
Comment 1 Markus Mohrhard 2012-01-30 10:07:45 UTC
This is not a bug. We added this behavior to fix several bugs in calc's copy/paste between different documents.

Calc now creates external references to the old file if the referenced content is not copied.

The difference between your two formulas is that Sheet1.A1 has a relative sheet reference while $Sheet1.A1 is an aboslute sheet reference. We correct behvior for absolut sheet references is to make the references external because with an aboslute reference you explicitly state that you always want to reference this content.
Comment 2 Rainer Bielefeld Retired 2012-01-30 13:19:31 UTC
For me that is a terrible mess, nothing else. Where is the switch to disable that feature?
Comment 3 Markus Mohrhard 2012-01-30 14:40:41 UTC
Hello Rainer,

there is no option to turn this behavior of. The old behavior was terribly wrong.

Create a document with: Sheet1, Sheet2, Sheet3 and another one with: Sheet3, Sheet1, Sheet2 and now copy the formula =$Sheet1.$A$2 from Sheet2 of the first document to the second document.

One suggestion from Laurent for 3.6 is to add a Paste special that does a paste and adjust absolute sheet refs by name but we have nothing in this direction for 3.5.
Comment 4 famo 2012-03-06 11:09:49 UTC
(In reply to comment #1)
> ... We correct behvior for absolut sheet references is to make the references
> external because with an aboslute reference you explicitly state that you 
> always want to reference this content.
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
No, this would only apply if it would be an absolute document reference, i.e. $'Untitled1'#$Tabelle1.C12 - but Rainers formula is just an absolute sheet reference.
So there is an absolute sheet reference before copying, but after insert its an absolute document reference - its still not really clear why this has to be the case...


(In reply to comment #3)
> Create a document with: Sheet1, Sheet2, Sheet3 and another one with: Sheet3,
> Sheet1, Sheet2 and now copy the formula =$Sheet1.$A$2 from Sheet2 of the first
> document to the second document.
I don't quite get your example, what could possible go wrong here?
To me the expected behavior is quite clear:
The formula remains the same =$Sheet1.$A$2, if there is no Sheet1 in the target document there would be a #REF error then.

I have no doubts there were/are errors before. However by converting the absolute sheet formula to an absolute document formula, "you" just seem to circumvent the underlying problem and thus passing it back to "us"/the user. :-(
Comment 5 famo 2012-03-15 10:38:56 UTC
hey Markus,
any thoughts on my comment #4 ?

I tested your example from comment #3:
> Create a document with: Sheet1, Sheet2, Sheet3 and another one with: Sheet3,
> Sheet1, Sheet2 and now copy the formula =$Sheet1.$A$2 from Sheet2 of the first
> document to the second document.

My guess of the problem:
In the formulas names are used for referencing to the sheets. However internally the sheets are referenced by ID:
 Name  |  ID
Sheet1 |  0
Sheet2 |  1
Sheet3 |  2

This means the formula =$Sheet.A1 does not reference to the sheet with the name "Sheet1" (what would one actually expect), but to the first sheet of the document (the one with ID 0).

If this formula is copied now to another document, for example such as this one:
 Name  |  ID
Sheet3 |  0
Sheet1 |  1
Sheet2 |  2

The formula =$Sheet1.A1 converts to =$Sheet3.A1, that is unexpected.


A sane solution would be here to keep the name and accordingly the name reference on copying. Is this technical feasible?

What do you think? I would plead for REOPEN of this bug.
Comment 6 Markus Mohrhard 2012-03-15 10:46:11 UTC
Yes we use the index for sheet refs and no this is not a bug. We discussed this problem in the calc team and decided to implement this solution.
Comment 7 Michael Meeks 2012-03-16 04:10:06 UTC
I wonder where these absolute sheet references get created - at least, in my limited experience I very seldom create such references.

Is there some tool that does that ? do people manually enter them ? (if so why?)

I'm trying to better understand the use-case here. Are there some corner cases that we could implement to make this better ? eg. selecting the sheet and moving it wholesale with "Move/Copy sheet" appears to do what you want - as a workaround (rather than using a different LibreOffice) I assume [with some work] we could make that move/copy multiple sheets at once - would that solve the issue ?
Comment 8 Markus Mohrhard 2012-03-16 04:36:51 UTC
So let me explain the reasons for this solution once in all detail.

Copying sheet references between different sheets is a bit of a pain. 

Internally we use an index to indentify sheets but these don't need to be the same in different documents for the same sheet. So the simple index based copy/paste can't work.

Using the sheet name is only a little bit better because you will only get useful results if you copy between similar documents. That means documents that more or less are in a direct relation, have the same structure and use the same names for the same data areas. If you copy between documents that are unrelated but still have the same sheet names you get just crap.

Since we have two different sheet addressing modes we( Eike, Kohei and I ) decided that the sanest solution is to keep relative sheet ranges as they are and transform absolute sheet references to external ones pointing to the old data.

The ideas behind this solution is that marking a sheet reference as absolute is a decision by the user where he says that he always wants to refer to this sheet. We further decided that references to the copied area will be preserved because they still point to the same data. So copying a whole sheet will not transform the absolute sheet refs pointing to this sheet, only the absolute sheet refs pointing to other sheets.

Then ther are relative sheet refs and these are not transformed during a copy/paste between different documents. They still behave like our old absolute sheet ref behavior.
Comment 9 Rainer Bielefeld Retired 2012-03-16 05:44:06 UTC
@Michael:
I explained on "[de-users] Workaround für "Bug 45385 - EDITING: Copy Paste formula to different document adds souce document filename to references"" what my problem is Short summarize:
I use spreadsheets following my internal standard with data in sheet1 and various calculations based on that data in other sheets of the document. When I had a new idea for a calculation in a newdocument.ods I was able to copy paste the cells with that new idea to an other spreadsheet olddocument.ods follwing the same standard, and I immediately was able to use the new calculation for the old data because the references in the calculation now lead to the project related values in olddocument.ods#seet1. Starting with 3.5 the pasted cells' references will be from  olddocument.ods to source data in different newdocument.ods#seet1, what is useless for me. 

I additionally sometimes use combinations of the standard data from sheet1 with additional data on calculation sheet, and often I have to do an alternative calculation on a different sheet with same source data from sheet1 and different data on calculation sheet, and so I need the absolute reference even within the document to sheet1, and additionally when I copy parts of a sheet (with data on that sheet) do an other document. 

I believe I do that 1000 times or more a year, and so the find and replace workaround is really annoying.

Until now I did not find the time to test whether the proposed workaround with named ranges would work.
Comment 10 stragu 2014-04-30 05:34:17 UTC
Hi Rainer

You might be interested in the bug report I recently created:

Bug 78074

Cheers