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

Assuming the 8 or 9 is in cell A1 your formulae can be something like this

=SUM($C$5:INDIRECT("$C$" & A1))

"David S" wrote:

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

Thank you for your help. Sooo simple! But nothing I tried before would work.

--
David S


"RonaldoOneNil" wrote:

Assuming the 8 or 9 is in cell A1 your formulae can be something like this

=SUM($C$5:INDIRECT("$C$" & A1))

"David S" wrote:

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

Hi David

As an alternative to using the volatile Indirect function, you could use
the faster and non-volatile Index function

=SUM($C$5:INDEX(C:C,A1))

The Index part, returns the cell in column C represented by the value in A1
--
Regards
Roger Govier

David S wrote:
Thank you for your help. Sooo simple! But nothing I tried before would work.

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

.

Hi,

You may select C4:C8 (row 4 is the header row) and convert it to a
List/Table by pressing Ctrl+L. When you convert a range to a List/Table, it
auto expands. Now when you add any data in row 9, all formulas in the
workbook will expand to include the 9th row.

The List feature was introduced from Excel 2003

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"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