Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to prevent auto scaling of header and footer when printing | Excel Discussion (Misc queries) | |||
How to prevent the auto resizing of a column (ie, lock the width) | Excel Discussion (Misc queries) | |||
Scroller Incrementation | Charts and Charting in Excel | |||
How do I prevent Excel from auto-correcting the date format? | Excel Discussion (Misc queries) | |||
prevent auto convert format | Excel Discussion (Misc queries) |