Absolute address problem

I like to copy cell A1 in Sheet1 into cell A1 in Sheet2
So in cell A1 in Sheet2 I put the following =Sheet1!$A$1
I used obsolute addresses – but when I cut and paste A1 in sheet1 into new location – reference address in Sheet2 changed also.
What I’m doing wrong?
Thx in advance
Amada

An example workbook?

If you want Sheet2!A1 to be equal to contents of Sheet1!A1 at all times then:

Sheet2!A1
=INDEX(Sheet1!$1:$65536,ROW(),COLUMN())

or

=INDIRECT("Sheet1!RC",FALSE)

Thanks a lot. It is exactly what I need.
Just two more questions:
Is it possible to have also “copy” of cell formatting – not only value?
Why my formula with $ sign doesn’t work?
Thanks in advance
Amada

Originally Posted by Amada

Is it possible to have also “copy” of cell formatting – not only value?

Realistically - not without VBA.
Though you can copy and paste the formats beforehand should you modify the formatting on Sheet1 thereafter it won't be reflected automatically on Sheet2.
(also you can ape some basic formatting with formulae but it's a very limited approach and is unlikely to be viable)

Originally Posted by Amanda

Why my formula with $ sign doesn’t work?

As you are physically relocating a target which is explicitly referenced so the range reference adapts accordingly.

Both INDEX & INDIRECT in effect use implicit references and as such the references do not adjust in the same way.

Only if Column A is itself cut / deleted etc in it's entirety - assuming you correct the sheet reference

I assumed the formula would be applied to more than one cell and thus opted for a version that could be used anywhere - in theory at least!)