Updating Formulas
 

I have a large spreadsheet that is maintained by 3 people. This spreadsheet
is broken into several sections with subtotals for each section. One of us
has a habit of adding rows at the end of a section and forgetting to update
the formulas. I usually learn of this error when I am preparing reports that
do not balance.

My practice is to add rows before the last row; move the last data row up to
the first new blank row than add the new lines in the new blank rows. There
has to be a better way to add rows at the bottom and have the formulas
automaticlly updated.

We are using 2003€¦Does anyone know of method/solution for adding rows at the
bottom of a range and having the formulas automatically updated to include
the new rows?

--
JPS

Updating Formulas
 

You should try to place and constuct formulas so they do not require updating.

A simple example:

Say we have data in column A and want to sum the data. Values go in A1 thru
A99 and in A100, the formula:

=SUM(A1:A100)

Whenever a new row of data is added, the formula must be updated. Another
approach is to put the formula:

=SUM(A2:A65536)

in A1 and have the data start in A2. Using this approach avoids the need
for updating the formula.
--
Gary''s Student - gsnu200794


"JPS" wrote:

I have a large spreadsheet that is maintained by 3 people. This spreadsheet
is broken into several sections with subtotals for each section. One of us
has a habit of adding rows at the end of a section and forgetting to update
the formulas. I usually learn of this error when I am preparing reports that
do not balance.

My practice is to add rows before the last row; move the last data row up to
the first new blank row than add the new lines in the new blank rows. There
has to be a better way to add rows at the bottom and have the formulas
automaticlly updated.

We are using 2003€¦Does anyone know of method/solution for adding rows at the
bottom of a range and having the formulas automatically updated to include
the new rows?

--
JPS

Updating Formulas
 

One more for your perusal.

=SUM(A1:INDEX(A:A,ROW()-1))

Entered in any cell from A2 down.

Inserted rows will get picked up.


Gord Dibben MS Excel MVP

On Fri, 11 Jul 2008 03:18:02 -0700, JPS wrote:

I have a large spreadsheet that is maintained by 3 people. This spreadsheet
is broken into several sections with subtotals for each section. One of us
has a habit of adding rows at the end of a section and forgetting to update
the formulas. I usually learn of this error when I am preparing reports that
do not balance.

My practice is to add rows before the last row; move the last data row up to
the first new blank row than add the new lines in the new blank rows. There
has to be a better way to add rows at the bottom and have the formulas
automaticlly updated.

We are using 2003…Does anyone know of method/solution for adding rows at the
bottom of a range and having the formulas automatically updated to include
the new rows?