How can I easily update formulas that include new rows?

  • Thread starter Thread starter OldKenGoat
  • Start date Start date
O

OldKenGoat

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
 
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>)
 
Back
Top