How can I easily update formulas that include new rows?
 

I have a worksheet that I use to update league baseball game stats on a
daily basis. Each day as many as 13 new rows are added to the bottom. I then
have to go to the top of 10 separate columns one at a time to update SUM
formulas so that they will include the new rows. For example, Col C may have
a formula at the top of the col that reads: =SUM(C4:C300). After the
addition of say 9 rows, I have to manually change the formula to read:
=SUM(C4:C309). Is there a way to type that 309 in a blank cell just once and
have all the SUM formulas refer to it?

TIA for your help.
OldKenGoat

Hi OldKenGoat,

If column A has an entry for every row, from A4 to A309, then the formula
=SUM(OFFSET(C4,0,0,COUNTA(Sheet1!$A:$A),1))
will sum cells C4:C309.

Works by counting all entries in column A. So the OFFSET formula says;
give me a range starting with cell C4 of height 309 rows and width 1 column.

Ed Ferrero
http://edferrero.m6.net

I have a worksheet that I use to update league baseball game stats on a
daily basis. Each day as many as 13 new rows are added to the bottom. I
then have to go to the top of 10 separate columns one at a time to update
SUM formulas so that they will include the new rows. For example, Col C may
have a formula at the top of the col that reads: =SUM(C4:C300). After the
addition of say 9 rows, I have to manually change the formula to read:
=SUM(C4:C309). Is there a way to type that 309 in a blank cell just once
and have all the SUM formulas refer to it?

TIA for your help.
OldKenGoat

Perhaps another way ..

Instead of in say, C1: =SUM(C4:C300),
maybe just extend the sum range all the way down
and use in C1: =SUM(C4:C65536)
(that'll last almost 14 years at the rate of +13 rows per day <g)
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"OldKenGoat" wrote in message
...
I have a worksheet that I use to update league baseball game stats on a
daily basis. Each day as many as 13 new rows are added to the bottom. I
then
have to go to the top of 10 separate columns one at a time to update SUM
formulas so that they will include the new rows. For example, Col C may
have
a formula at the top of the col that reads: =SUM(C4:C300). After the
addition of say 9 rows, I have to manually change the formula to read:
=SUM(C4:C309). Is there a way to type that 309 in a blank cell just once
and
have all the SUM formulas refer to it?

TIA for your help.
OldKenGoat