Prevent cell/array references from changing when altering/moving thecell/array

[nme]

Hello,

In Sheet1, I have a reference like this: =SUM(Sheet2!A1:A100)

Sheet2 contains 100 rows of static data, no formulas, copied and
pasted into the sheet from another system.

However, when I delete and/or manipulate the data in Sheet2, the
reference on Sheet1 is sometimes modified.

(e.g. when I delete a row in the middle of Sheet2, the reference on
Sheet1 now only refers to A1:A99.

Is it possible to avoid this behaviour? I want the A1:A100 reference
to be absolute, and never change (unless I do it manually) - no matter
what I do in Sheet2.

Same thing happens when I refer to a single cell, e.g. C1, and then
cut and paste the C1 cell to C3. The reference now points to C3, even
though I still want it to point to C1 (even though C1 now is empty).

Thanks for any help


Kind regards,
Nils Magnus

 

[Bernie Deitrick]

Niles,

You can use the INDIRECT function to absolutely freeze a reference:

=SUM(INDIRECT("Sheet2!A1:A100"))

HTH,
Bernie
MS Excel MVP