How to fix an array or range
 

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

How to fix an array or range
 

Try =SUM(INDIRECT("A6:A70"))
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"delboy" wrote in message
...
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