A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Automatic update to SUM formula cell references.



 
 
Thread Tools Display Modes
  #1  
Old April 20th, 2010, 03:51 PM posted to microsoft.public.excel.worksheet.functions
David S
external usenet poster
 
Posts: 59
Default Automatic update to SUM formula cell references.

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
  #2  
Old April 20th, 2010, 04:06 PM posted to microsoft.public.excel.worksheet.functions
RonaldoOneNil
external usenet poster
 
Posts: 345
Default Automatic update to SUM formula cell references.

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

  #3  
Old April 20th, 2010, 04:21 PM posted to microsoft.public.excel.worksheet.functions
Don Guillett[_2_]
external usenet poster
 
Posts: 607
Default Automatic update to SUM formula cell references.

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


  #4  
Old April 20th, 2010, 06:23 PM posted to microsoft.public.excel.worksheet.functions
David S
external usenet poster
 
Posts: 59
Default Automatic update to SUM formula cell references.

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

  #5  
Old April 20th, 2010, 06:24 PM posted to microsoft.public.excel.worksheet.functions
David S
external usenet poster
 
Posts: 59
Default Automatic update to SUM formula cell references.

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


.

  #6  
Old April 21st, 2010, 12:16 PM posted to microsoft.public.excel.worksheet.functions
Roger Govier[_8_]
external usenet poster
 
Posts: 338
Default Automatic update to SUM formula cell references.

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.

  #7  
Old April 21st, 2010, 12:21 PM posted to microsoft.public.excel.worksheet.functions
Ashish Mathur[_2_]
external usenet poster
 
Posts: 1,764
Default Automatic update to SUM formula cell references.

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


 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 09:46 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.