Updating Formulas

[JPS]

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?

 

[Gary''s Student]

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.

 

[Gord Dibben]

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