Automatic update to SUM formula cell references.

[David S]

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!

 

[RonaldoOneNil]

Assuming the 8 or 9 is in cell A1 your formulae can be something like this

=SUM($C$5:INDIRECT("$C$" & A1))

 

[Don Guillett]

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)

 

[David S]

Thank you for your help. Sooo simple! But nothing I tried before would work.

 

[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.

 

[Roger Govier]

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

 

[Ashish Mathur]

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