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. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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
|
|||
|
|||
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 |
#4
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 | |
|
|