[Solved] #REF! when using Paste Special or Paste in Calc

[Bob Hawkins]

I have maintained a weekly budget spreadsheet of income and expenditure in MS Works for many years. I recently saved my MS Works .xlr file as .ods because I wish to gradually migrate to OpenOffice. The four main columns in the spreadsheet are:
Column A Item (Electric, for example)
Column B Previous Amount (Format = Number)
Column C Weekly Rate (Format = Number)
Column D Income (Format = Number)
Column E Expenditure (Format = Number)
Column F New Amount (Formula =B7+C7+D7-E7, for example)
Each week in MS Works, I would select the whole of column E, Ctrl C, highlight B7 (the cell of the first row), choose Paste Special, and the whole of column B and column E would be updated.
I find that, when I try to do the same thing in Calc, whether I use Paste Special or Paste, the result is #REF! displayed in the cells and =#REF!7+#REF!7+#REF!7-A7 (for example) in the Input line.

I see now that Paste all, which includes Formulas, was selected by default in the Paste Special dialog box. Once I uncheck all but the Numbers box, the issue is resolved.
Also, I note that, as a result of highlighting the Input line details to copy and paste into this post, I cannot unhighlight it; I have to close the file.
I should be most grateful if someone could advise me about what I am doing incorrectly.

[Villeroy]

Well, when you paste values only you will lose the formulas and thus you lose the relation between the raw input values and the calculated results.

The #REF! error in spreadsheets can be triggered like this:
Put a reference =A1 in B2. This relative reference refers to the cell one row above and one column to the right. Copy B2 to B1 and A2 and see what happens.

So you pasted relative references from one sheet to a position in another sheet where the references refer to a place out of sheet-bounds.
_________________
Rest in peace, oooforum.org
Get help on http://forum.openoffice.org