vba insert row does not update Sum formula
 

Hi,

I have code that inserts a row for additional data that needs to be summed.
But the sum formula below this new row does not update to include the newly
increased number of rows.

The only thing I have found in the forums is someone's suggestion to always
leave a blank row just above the sum formula, and insert new rows above it.

I have not tried this because it seems most inelegant!

It seems there must be a way this can be done.

Thanks for any insights you may have.

Harold

vba insert row does not update Sum formula
 

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

Entered below current data in column A

As you insert rows above and add new values, the SUM range will include the
new rows and values.


Gord Dibben MS Excel MVP

On Thu, 20 Aug 2009 15:31:15 -0500, "Harold Good"
wrote:

Hi,

I have code that inserts a row for additional data that needs to be summed.
But the sum formula below this new row does not update to include the newly
increased number of rows.

The only thing I have found in the forums is someone's suggestion to always
leave a blank row just above the sum formula, and insert new rows above it.

I have not tried this because it seems most inelegant!

It seems there must be a way this can be done.

Thanks for any insights you may have.

Harold

vba insert row does not update Sum formula
 

Thanks very much, I hadn't thought of doing it with a different formula.

Harold

"Gord Dibben" <gorddibbATshawDOTca wrote in message
...
=SUM(A1:INDEX(A:A,ROW()-1))

Entered below current data in column A

As you insert rows above and add new values, the SUM range will include
the
new rows and values.


Gord Dibben MS Excel MVP

On Thu, 20 Aug 2009 15:31:15 -0500, "Harold Good"
wrote:

Hi,

I have code that inserts a row for additional data that needs to be
summed.
But the sum formula below this new row does not update to include the
newly
increased number of rows.

The only thing I have found in the forums is someone's suggestion to
always
leave a blank row just above the sum formula, and insert new rows above
it.

I have not tried this because it seems most inelegant!

It seems there must be a way this can be done.

Thanks for any insights you may have.

Harold