Automatic update to SUM formula cell references.
in [Functions]
|
Prev: Vlookup issue
Next: Count If.....help
From: Roger Govier on 21 Apr 2010 07:16 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. >
From: Ashish Mathur on 21 Apr 2010 07:21
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" <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 |