How to fix an array or range

[delboy]

I want to fix a range e.g. =sum(a6:a70) so that when i delete a row at the
top of the range and add a row at the bottom the formula points to the same
cell references i.e. the range does not change and stays at a6:a70.

Similarly, if a cell on another worksheet points to this range e.g.
=AVERAGE(a6:a:70) this reference stays the same after the deletion and
addition of a row at the top and bottom of the range

 

[Bernard Liengme]

Try =SUM(INDIRECT("A6:A70"))
best wishes