Ask Your Question
0

Reference to other sheets shifter whenever I copy the sheet

asked 2016-02-15 15:13:07 +0200

TangoMango gravatar image

updated 2016-03-09 11:39:53 +0200

Alex Kemp gravatar image

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/detai... (you may use the email address glanor@mailcatch.com to download the file and visit its inbox here : http://mailcatch.com/en/temporary-inb... )

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.

edit retag flag offensive close merge delete

2 Answers

Sort by » oldest newest most voted
0

answered 2016-05-01 13:28:20 +0200

TheHag gravatar image

updated 2016-09-27 11:05:02 +0200

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

edit flag offensive delete link more
0

answered 2016-11-23 05:30:41 +0200

Hasan gravatar image

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.

edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2016-02-15 15:13:07 +0200

Seen: 1,186 times

Last updated: Nov 23 '16