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

Better yet set up a defined name for the range and use that
while on the desired sheet>insert>name>define>in 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
(E-Mail Removed)
"David S" <(E-Mail Removed)> wrote in message
news:0E666D00-07A5-4239-BE1E-(E-Mail Removed)...
>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

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 sheet>insert>name>define>in 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
> (E-Mail Removed)
> "David S" <(E-Mail Removed)> wrote in message
> news:0E666D00-07A5-4239-BE1E-(E-Mail Removed)...
> >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.
>

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" <(E-Mail Removed)> wrote in message
news:0E666D00-07A5-4239-BE1E-(E-Mail Removed)...
> 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