Prevent auto incrementation of formula
 

Does anyone know of a way to prevent formula from automatically updating
themselves when new cells are added into a range.
I'm trying to base a calculation on the first 20 cells in a range of cells,
additional cells will be inserted at the start of this range, but I don't
want the formula to update the range past the first 20 cells. (I'm getting
an average of the 20 latest figures to be entered, if that helps). Thanks in
advance.

Prevent auto incrementation of formula
 

"Marie Bayes" wrote:

Does anyone know of a way to prevent formula from automatically updating
themselves when new cells are added into a range.

Marie

In a blank cell type the range you want to sum e.g. A1:A20 Then enter the
formula
=SUM(INDIRECT(C1)) where C1 is the cell in which you entered the range to be
summed.

You can change the font colour to white so that it is hidden and not shown
in a print.

Regards
Peter

Prevent auto incrementation of formula
 

Absolutely Brilliant, thanks Billy it works a treat!

"Billy Liddel" wrote:



"Marie Bayes" wrote:

Does anyone know of a way to prevent formula from automatically updating
themselves when new cells are added into a range.

Marie

In a blank cell type the range you want to sum e.g. A1:A20 Then enter the
formula
=SUM(INDIRECT(C1)) where C1 is the cell in which you entered the range to be
summed.

You can change the font colour to white so that it is hidden and not shown
in a print.

Regards
Peter

Prevent auto incrementation of formula
 

Here is an example, you can modify it for your formula
=AVERAGE(INDIRECT("A4:A23")
best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Marie Bayes" wrote in message
...
Does anyone know of a way to prevent formula from automatically updating
themselves when new cells are added into a range.
I'm trying to base a calculation on the first 20 cells in a range of
cells,
additional cells will be inserted at the start of this range, but I don't
want the formula to update the range past the first 20 cells. (I'm
getting
an average of the 20 latest figures to be entered, if that helps). Thanks
in
advance.

Prevent auto incrementation of formula
 

Marie

Thanks for the feedback and rating.

Peter

"Marie Bayes" wrote:

Absolutely Brilliant, thanks Billy it works a treat!

"Billy Liddel" wrote:



"Marie Bayes" wrote:

Does anyone know of a way to prevent formula from automatically updating
themselves when new cells are added into a range.

Marie

In a blank cell type the range you want to sum e.g. A1:A20 Then enter the
formula
=SUM(INDIRECT(C1)) where C1 is the cell in which you entered the range to be
summed.

You can change the font colour to white so that it is hidden and not shown
in a print.

Regards
Peter

Prevent auto incrementation of formula
 

Thanks Bernard, this one worked a treat too...

"Bernard Liengme" wrote:

Here is an example, you can modify it for your formula
=AVERAGE(INDIRECT("A4:A23")
best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Marie Bayes" wrote in message
...
Does anyone know of a way to prevent formula from automatically updating
themselves when new cells are added into a range.
I'm trying to base a calculation on the first 20 cells in a range of
cells,
additional cells will be inserted at the start of this range, but I don't
want the formula to update the range past the first 20 cells. (I'm
getting
an average of the 20 latest figures to be entered, if that helps). Thanks
in
advance.