Cell references

[Guest]

I have two sheets in a workbook. On sheet2, the data in lot of cells come
from the cells on sheet1. The problem is that when I move the data on sheet1,
the cell references on sheet2 also change in formula, which means they are
bound with data, not with the cell address. I want to ensure that the cell
references in formulas on sheet2 do not change, when I move data from the
referred cells on sheet1. Can anyone help me to sort out this problem?

 

[Guest]

I have two sheets in a workbook. On sheet2, the data in lot of cells come
from the cells on sheet1. The problem is that when I move the data on sheet1,
the cell references on sheet2 also change in formula, which means they are
bound with data, not with the cell address. I want to ensure that the cell
references in formulas on sheet2 do not change, when I move data from the
referred cells on sheet1. Can anyone help me to sort out this problem?

One way ..

In Sheet2,

Instead of using in say, A1: =Sheet1!A1
with A1 copied across / down to link to cells in Sheet1

Try in A1:
=OFFSET(INDIRECT("'Sheet1'!A1"),ROW(A1)-1,COLUMN(A1)-1)
Copy A1 across / down as before to link in the same manner to Sheet1

The formulas will now always point to the same cells in Sheet1, eg: A1 will
always point to Sheet1's A1, B1 to Sheet1's B1, and so on, irrespective of
any subsequent "moves" of data in Sheet1's A1, B1, etc to somewhere else

---