Automatic update to SUM formula cell references.
in [Functions]
|
Prev: Vlookup issue
Next: Count If.....help
From: David S on 20 Apr 2010 10:51 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 20 Apr 2010 11:06 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 20 Apr 2010 11:21 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 20 Apr 2010 13:23 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 20 Apr 2010 13:24
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 > > . > |