Bug 50860 - EDITING: Copy Sheet Moves Formulas
Summary: EDITING: Copy Sheet Moves Formulas
Status: RESOLVED DUPLICATE of bug 85538
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
3.5.3 release
Hardware: Other All
: medium normal
Assignee: Not Assigned
URL:
Whiteboard: BSA
Keywords:
Depends on:
Blocks:
 
Reported: 2012-06-07 15:13 UTC by davidoverend
Modified: 2017-04-19 05:40 UTC (History)
3 users (show)

See Also:
Crash report or crash signature:

Attachments
Example.ods (7.41 KB, application/vnd.oasis.opendocument.spreadsheet)
2012-06-21 08:04 UTC, davidoverend 		Details
View All Add an attachment (proposed patch, testcase, etc.)

Note You need to log in before you can comment on or make changes to this bug.
Description davidoverend 2012-06-07 15:13:59 UTC 
Problem description: 
I copied a sheet that contains references to other sheets.  If the sheet is copied immediately after the original it seems to work, but if it is copied further down the group of tabs the formulas are moving vertically, losing references, and disappearing.
Steps to reproduce:
1. Created spreadsheet with references to other sheets
2. Copied sheet 
3. Formulas not where they should be

Current behavior:
Not working
Expected behavior:
Working
Platform (if different from the browser): 
              
Browser: Mozilla/5.0 (Windows NT 6.1; WOW64) AppleWebKit/536.5 (KHTML, like Gecko) Chrome/19.0.1084.52 Safari/536.5
Comment 1 bfoman (inactive) 2012-06-21 06:09:38 UTC 
Created attachment 63308 [details]
Example.ods

Could you attach any example documents to allow others to check on different
system/build?
Comment 2 davidoverend 2012-06-21 08:04:33 UTC 
The behavior I was dealing with is the same as found in the following 
forum thread and is apparently the standard behavior for formulas 
referring to a cell on a different sheet when the sheet is copied.  The 
use of absolute references is an easy resolution, but personally I find 
the behavior a little counter intuitive.  Overall I love calc and have 
been using it for years at home and have been advocating a change at the 
company I work for, but things like this make adoption harder to argue 
for.  When long term spreadsheet users find what appear to be problems 
with basic functions, it scares them off and the odds of getting a 
second look are slim.  I'm not advocating that calc be a clone of excel 
by any means, as there are some things that I prefer about calc, but 
some things should behave in the same manner.  Please keep up the good 
work and if there is anything I can do to help (I'm not a programmer) 
let me know.

http://user.services.openoffice.org/en/forum/viewtopic.php?t=47863 
<http://user.services.openoffice.org/en/forum/viewtopic.php?t=47863>

Steps to reproduce problem:
1) Open attached spreadsheet
2) Copy Sheet 1 to the position after Sheet 2
3) Compare formulas between Sheet 1 and the copy (I expected them to be 
the same)

Thanks,
David


On 6/21/2012 8:09 AM, bugzilla-daemon@freedesktop.org wrote:
> https://bugs.freedesktop.org/show_bug.cgi?id=50860
>
> bfoman<bfo.bugmail@spamgourmet.com>  changed:
>
>             What    |Removed                     |Added
> ----------------------------------------------------------------------------
>                   CC|                            |bfo.bugmail@spamgourmet.com
>
> --- Comment #1 from bfoman<bfo.bugmail@spamgourmet.com>  2012-06-21 06:09:38 PDT ---
> Could you attach any example documents to allow others to check on different
> system/build?
>
Comment 3 Noel Grandin 2012-09-05 12:50:24 UTC 
Tested in 3.6.1.2 (Windows)

The formulas are not moving, but sheet references are being adjusted, when they arguably should not.
Comment 4 lucy 2012-09-12 07:35:25 UTC 
I have same problem -using version 3.5.6.2 (on Windows 7)
Producing summary sheets referenced to data in 16 sheets.
Arranging summary data from each sheet in rows - in the same order as sheets in file. The row order of the references become jumbled randomly but the references within the row remain the same and rest of data copies correctly (including formulas with in the rows) 
A problem because in my case the rows are then labelled as containing data from another sheet -just glad I realised it had happened. 
  
Copy sheet next to original works, copy in another sheet position references become jumbled. Repeated copying in same position maintains the same jumbled order, copying into another sheet position jumbles reference but in a different order.
My sheet references have format  =sheetname.$C$122 - changing to =$sheetname.$C$122 stops the jumbling but shift F4 does seem to add the $ to the sheet name and if this is required it should happen when you are linking to another a sheet... 
(sorry can't upload file as it contains personal client info)
Comment 5 Kevin Suo 2013-12-05 06:12:39 UTC 
The formular changes when copying sheets because calc treated sheet name as relative reference by default. The same happen if you copy a cell formular in sheet1 to sheet4 while the cell formular is referenced to sheet2.

However, when someone is copying sheets, or copying cell formulars between sheets, no one is expecting the sheet name to be changed.Change sheet name will do only harm, no good.

So, Calc should treat sheet name as absolute reference by default.
Comment 6 Kevin Suo 2013-12-05 06:18:14 UTC 
Adding 61696,43175 and 69181 to see also list. They are related.
Comment 7 Kevin Suo 2013-12-05 06:21:29 UTC 
*** Bug 69181 has been marked as a duplicate of this bug. ***
Comment 8 anto 2014-10-21 16:43:59 UTC 
Version 4.3.2.2
Same problem, when a formula with a link another sheet, when copying a sheet the formula are different.
Comment 9 Kevin Suo 2014-10-22 00:25:08 UTC 
Version field should be the earlist version the bug appears. Changing back to 3.5.3.
Comment 10 QA Administrators 2015-12-20 16:12:58 UTC Comment hidden (obsolete)
Comment 11 QA Administrators 2017-01-03 19:48:19 UTC Comment hidden (obsolete)
Comment 12 Kevin Suo 2017-04-19 05:40:13 UTC 

*** This bug has been marked as a duplicate of bug 85538 ***