From: David S on
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
From: RonaldoOneNil on
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
From: Don Guillett on
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
dguillett(a)gmail.com
"David S" <DavidS(a)discussions.microsoft.com> wrote in message
news:0E666D00-07A5-4239-BE1E-494BB43FCB70(a)microsoft.com...
>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

From: David S on
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
From: David S on
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
> dguillett(a)gmail.com
> "David S" <DavidS(a)discussions.microsoft.com> wrote in message
> news:0E666D00-07A5-4239-BE1E-494BB43FCB70(a)microsoft.com...
> >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
>
> .
>
 |  Next  |  Last
Pages: 1 2
Prev: Vlookup issue
Next: Count If.....help