Reference to other sheets shifter whenever I copy the sheet

[TangoMango]

Hello.

I am facing an issue : whenever I copy a sheet inside a calc file, all references to another sheet of the same file are shifted.

Here is an example : http://www.filehosting.org/file/details/543863/Test.ods
(you may use the email address glanor@mailcatch.com to download the file and visit its inbox here : Disposable Temporary Email Addresses - AntiSpam Temporary Inbox at MailCatch.com - Inbox )

In sheet 2 is a cell with a value inside cell B1 (163).
In sheet 1, there are two cells referring to the cell B1 of sheet 2 : cells B3 and B4.

If I copy the sheet 1 and place the copy at the beginning (before sheet 1), its cell B3 will be referring to cell B1 of sheet 1, not of sheet 2, while the its cell B4 will correctly refer to the cell B1 of sheet 2.

It took me a while to understand that I must add the ‘$’ sign in front of the sheet name in the formula to avoid the shift of sheet value while copying a sheet.
Is there a way to set using absolute sheet values as the default behaviour in Calc (but not using absolute references for cells) ?

And at the same time, I have another question related to it : several times it happened to me that inside the formulas of the sheet in which I am working, all references to other cells of the same sheet are changed from “=B1C1" to "=Sheet.B1Sheet.C1” without asking me for it. I think it happens after I save and open again the file.

This bothers me because once it is done, I cannot benefit any longer from the function where, when I double-click a cell, the names of referenced cells in the formula get coloured and the matching cells are highlighted in the corresponding colours.
Any idea what is causing this ?

I use LO 5 under Linux Mint, Ubuntu and Windows 7.

[TheHag]

Hi TangoMango,

You reminded me about putting a $ in front of the sheet name, which fixed my problem. (I was coping the 9th sheet to the 3rd position so the shifting of the sheet reference gave rise to #REF! everywhere.) Thank you, however as all of my references to cells in external sheets were absolute I didn’t have the same problem as you.

I did experiment with copying the 1st sheet before itself. The sheet references shifted but all of the cell references stayed the same - e.g. old_3rd_sheet.D8 became new_3rd_sheet.D8, unlike your cell B4. I’m running LO Calc 5.0.5.2 under Win 10.

On your second question, it doesn’t happen all of the time on my spreadsheets - it seems quite random. I don’t know what causes it but the workaround only takes a couple of seconds to do. When it does happen I just ctrl&h, paste/type ‘sheetname.’ into the ‘Search For’ box, leave the ‘Replace With’ box empty and ‘Replace All’. Not only does it get F2/double_click working again, it makes reading cell formulae a lot easier too.

Regards,

John

[Hasan]

In the meantime, what John suggested in his experiment is a best solution. Move the sheet to the first tab, and copy it before itself. thank you John.