Better yet set up a defined name for the range and use that
while on the desired sheetinsertnamedefinein the name box type in colC
in the formula box type in
=offset($c$5,0,0,counta($c$c),1)
OR if numbers
=offset($c$5,0,0,match(9999999,$c$c),1)
or another formula to determine the last cell in col C. Look in the help
index for OFFSET
then use
=sum(colc)

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"David S" wrote in message
...
I have a spreadsheet that has many SUM formulas that change on a monthly
basis. Instead of manually changing each formula every month I want to
create a reference to pull the row number from one cell so that updates
each
SUM formula. For example, change SUM($C$5:$C$8) to SUM($C$5:$C$9), where
changing the 8 to a 9 in a cell outside the report range will update all
the
formulas. The SUM formulas are different for the columns, but use the
same
row reference. I used to be able to do this in Lotus, but have never
figured
out how to do this in Excel. We have Office 2007 software. Thank you
for
your help!
--
David S

This function is new to me and I can see where it will be very helpful once I
master its use. Thank you for your help.

--
David S


"Don Guillett" wrote:

Better yet set up a defined name for the range and use that
while on the desired sheetinsertnamedefinein the name box type in colC
in the formula box type in
=offset($c$5,0,0,counta($c$c),1)
OR if numbers
=offset($c$5,0,0,match(9999999,$c$c),1)
or another formula to determine the last cell in col C. Look in the help
index for OFFSET
then use
=sum(colc)

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"David S" wrote in message
...
I have a spreadsheet that has many SUM formulas that change on a monthly
basis. Instead of manually changing each formula every month I want to
create a reference to pull the row number from one cell so that updates
each
SUM formula. For example, change SUM($C$5:$C$8) to SUM($C$5:$C$9), where
changing the 8 to a 9 in a cell outside the report range will update all
the
formulas. The SUM formulas are different for the columns, but use the
same
row reference. I used to be able to do this in Lotus, but have never
figured
out how to do this in Excel. We have Office 2007 software. Thank you
for
your help!
--
David S

.