Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Update all formulas on spreadsheet - Apr05! to May05! | Excel Discussion (Misc queries) | |||
When I change my data, my formulas don't update the answers,why? | Excel Discussion (Misc queries) | |||
Excel formulas are slow to update | Excel Worksheet Functions | |||
specific rounding of formulas which may need to include an IF stat | Excel Worksheet Functions | |||
copy worksheet ... formulas do not update | Excel Worksheet Functions |