BUG: #REF while Copy&Paste a reference (CALC)

knel · Blue Pill Joined: Mar 02, 2009 Posts: 1

On Sheet1 cell A1 I add a text: qwerty. Now on Sheet2 I want to create a reference/link to Sheet1 A1, so if I change the text in Sheet1 A1, it will change in Sheet2. So I add at Sheet2 cell A8: ='Sheet1'.A1
Everything works fine, but now if I want to copy and paste the information from Sheet2 A8 to Sheet2 B9 I get a #REF or random information because CALC references to any cell from the same row, it adds a random number at the end.

Instead of adding 'Sheet1'.A1 when pasting it adds: 'Sheet1'.A18 or 'Sheet1'.A93, etc. every time I paste the number after the A changes.

I'm sorry if this was posted before, but I made a search and the list was so long that I couldn't check it completely.

PS: Sorry for my english is not my native language, I hope you could understand it.

James3359 · Posted: Tue Mar 03, 2009 4:22 am Post subject:

What you are describing sounds like normal spreadsheet behaviour, not a bug.

The formula you are pasting stores a relative value for the cell, so when you past the formula elsewhere, the default option is for it to assume that you want it to refer to another cell in the same relative position to the cell you are pasting in. If you want to keep an absolute value you can do it by placing a $ before each element of the cell's value e.g. $B$5. This will never change wherever you cut/copy and paste it to. (See this OpenOffice.org tutorial - NeoOffice is based on OpenOffice)

Alternatively, to retain cell references for a one-off copy/cut and paste use the following steps:

Select the cell you want to cut or copy from. The Formula Bar toolbar should show the contents of that cell in the Input Line
Copy the formula from the input line
Paste it into the cell where you want it

This process overrides/bypasses the updating of relative values pasted into new location.