Hello. I have source cell, that is used in other target formula, lets say the source cell is A1 and in cell A2 I have target formula =A1+1.
Now when I cut/paste content of A1 to other cell, to B1, the target formula in A2 changes from =A1+1 to =B1+1.
This is very annoying, bacuse it messes up my big tables when I move calculation source data. How to avoud target formula being chaged when I move source?
YOU messes up YOUR data if you move it criss-cross around by random!
=INDIRECT("A1")+1
That unnecessarily adds indirection though and is executed on each and every change of sheet content.
Copy, Delete, choose destination cell and Paste (Ctrl+C, Del, … Ctrl+V).
Adapting references to moved cells is all major spreadsheet implementations’ standard behaviour, it does not mess up things, it is expected.
You could define a named expression with a relative cell reference and use that in the cell formula expression instead of the cell reference. e.g. with the active cell being A2 hit Ctrl+F3 and in Manage Names use Add to define a name OneUp and assign it the expression/reference A1 (overwrite the preset $Sheet1.$A$2). Close dialog with Add and then OK. Then in cell A2 use the formula =OneUp+1.
This way, being defined on A2 and pointing to A1, the name OneUp always points to the cell immediately above wherever used.