[upstream] Spreadsheet sheet copy changes sheet reference in formulas

[Derek Abson]

Binary package hint: openoffice.org

Sheet reference translation error. After a sheet copy, sheet references are shifted by one. In other words sheet1.range becomes sheet2.range in thr sheet copy version.

derek@tm8100-laptop:~$ lsb_release -rd
Description: Ubuntu 7.10
Release: 7.10

derek@tm8100-laptop:~$ apt-cache policy openoffice.org-calc
openoffice.org-calc:
  Installed: 1:2.3.0-1ubuntu5.4
  Candidate: 1:2.3.0-1ubuntu5.4
  Version table:
 *** 1:2.3.0-1ubuntu5.4 0
        500 http://security.ubuntu.com gutsy-security/main Packages
        500 http://archive.ubuntu.com gutsy-updates/main Packages
        100 /var/lib/dpkg/status
     1:2.3.0-1ubuntu5 0
        500 http://archive.ubuntu.com gutsy/main Packages

[Derek Abson (dsabson)]

To protect a reference to sheet1, say, before copying sheet containing reference to sheet1, prefix sheet1 with $ sign. Then reference to sheet1 becomes $sheet1 in original sheet and remains $sheet1 in generated copy.

I'm not really sure if it should normally be necessary to protect references embedded in sheets to other sheets, but it is at least it's a work around!

[Chris Cheney (ccheney)]

Do you still have this problem with openoffice.org 1:2.4.1-1ubuntu2 in Ubuntu 8.04?

[Derek Abson (dsabson)]

Yes! I still have this bug in Ubuntu 8.04! But my version is now openoffice.org 1:2.4.0-3ubuntu6.

[Chris Cheney (ccheney)]

Derek,

You should be able to get 1:2.4.1-1ubuntu2 if you make sure that hardy-updates is in the list to download from. Once you upgrade to this version see if the problem still exists since it has a lot of other fixes from what was in the normal Ubuntu 8.04 (Hardy) release.

Thanks,

Chris Cheney

[Derek Abson (dsabson)]

Done the update to openoffice.org 1:2.4.1-1ubuntu2! Sorry, still doesn't work.

[Chris Cheney (ccheney)]

Ok, I see the document but can't seem to reproduce the problem you are having. If I insert a sheet it becomes "Sheet10" but it doesn't seem to affect any of the formulas, and if I copy for example "countLotto" to become "countLotto 2" it doesn't seem to affect any formulas either.

Can you explain in a little better detail what is going wrong?

Thanks,

Chris Cheney

[Derek Abson (dsabson)]

To reproduce the Copy error, in file lottoPermErr.ods, you must copy sheet, Results, say, to Results 2, then compare the functions that appear in the lotto and plus columns in both versions of the copied sheet. In the original version, Results, you will see functions: =countif(lotto.range;value) and =countif(plus.range;value) in the lotto and plus columns respectively. Whereas, in the copied version, Results 2, you will now see functions: =countif(plus.range;value) and =countif(countLotto.range;value) in the lotto and plus columns respectively. The sheet references have been corrupted in Results 2. If the sheet references are protected by being prefixed with a $ sign, sheets, Results and Results 2 are the same. I am not aware that sheet references need to be protected in this way. See the attached ods file.

[bs66 (bugsquad66)]

I would say that this is not a bug, its a feature.

The behavior is similar to how Microsoft Office Excel 2003 works when copying sheets.

/bs66

[bs66 (bugsquad66)]

ehrm. my mistake. i misunderstood the problem. sorry.

the behavior is NOT the same as in Microsoft Office Excel 2003.
Excel behaves as the reporter expects OOo to behave.

I can reproduce the behavior here.

0:~$ lsb_release -rd
Description: Ubuntu intrepid (development branch)
Release: 8.10
0:~$ apt-cache policy openoffice.org-calc
openoffice.org-calc:
  Installed: 1:2.4.1-9ubuntu2
  Candidate: 1:2.4.1-9ubuntu2
  Version table:
 *** 1:2.4.1-9ubuntu2 0
        500 http://archive.ubuntu.com intrepid/main Packages
        100 /var/lib/dpkg/status

[Neil Munro (neilmunro-deactivatedaccount)]

Since two people can confirm this bug exists, it has now been marked as confirmed.

[bs66 (bugsquad66)]

To reproduce:

http://launchpadlibrarian.net/16381493/lottoPermErr.ods

Open lottoPermErr.ods in Calc (the version that is 39.7 KiB. se attachment above)
Save the file locally (must do this in order to copy sheets)
Select the sheet "Results" (leftclick the tab at the bottom)
Rightclick the "Results" tab and select "Move/Copy Sheet..."
Change "Insert before" to "-move to end position-" and enable checkbox "Copy". Click OK.
A new shet tab was created named "Results 3"
Compare the formulas in cells C2 in the sheets "Results" and "Results 3".

The content in cell C2 in sheet "Results" is: =COUNTIF(lotto.$E$8:$K$8;B2)
But cell C2 in the sheet "Result 3" contains: =COUNTIF(countLotto.$E$8:$K$8;B2)
Expected behavior is that the formulas are the same, but they are not.

[bs66 (bugsquad66)]

Same problem with Ubuntu 8.04.1 and OpenOffice 3.0 RC3.

[bs66 (bugsquad66)]

Maybe already reported here?
http://www.openoffice.org/issues/show_bug.cgi?id=1715

[bs66 (bugsquad66)]

Bug still there in OOo 3.0 release (build 9358) on Windows.

[hackel (hackel)]

Just hit this in OO3.1 (1:3.1.1-5ubuntu1) in Karmic.

[hackel (hackel)]

Also happens with simply copy-paste, not just copying a whole sheet.

[Björn Michaelsen (bjoern-michaelsen)]

[This is an automated message.]
There are no new official OpenOffice.org releases in Ubuntu packaging anymore => Won't Fix

If the problem persists, please mark this bug as "also affects project Libreoffice" or "also affects distribution Libreoffice (Ubuntu)" if that has not happened already.

Please leave references to upstream OpenOffice.org bugs in place to allow cross pollination.