Prevent Formulas from changing due to cut and paste of linked cells

[John Broderick]

Say I have a 10x10 range of cells on Sheet1 in location A1:J10
On Sheet2 I want a (live) link to those cells in the same position i.e
whatever I see
in cells A1:J10 on sheet 1, I want to see the exact same thing in cells
A1:J10 on Sheet 2.
One way to do this is copy the range on Sheet1 and use Paste Link on Sheet2.
That is just what I want.

But now I want to be able to change the order of columns on the Sheet1
cells,
without changing the formulas on sheet 2.
So if I swap columns A and C (using Cut and Insert say) on sheet1
I want Sheet2 to reflect that, which it would do if the Sheet2 formulas
don't change.
But they change when I cut and insert the Sheet1 data to reorder it.

How do I stop the Sheet2 changes from happening when I make changes on
Sheet1?

Thanks
John

 

[JE McGimpsey]

One way:

=INDIRECT("Sheet1!A1")

 

[John Broderick]

That works, thanks.

Although to enter it for a large number of cells I had to write a macro,
as it does not update the address wiith copy down/across even though I
entered "A1" and not "A$1".