Post: Drimnagh, Dublin 12, Republic Of Ireland

Tel/Fax: 353-1-4548335 Email:dbassist@indigo.ie

<<< back to Excel Tips

Suppose there are numbers in cells A1:A10, and you have the formula =SUM(A1:A10) in cell A11.  You need to add another value to the list which must be included in the SUM formula. If you insert a row in the middle of the column, the SUM range expands automatically.

But a common scenario is the need to add the new value to the bottom of the list - when you insert at row 11, your formula must now be edited to include row 11.

One way around this problem is to create a named formula, using a relative reference, that refers to the cell one above. (Stay with me.)

In this example, cell A2 is the active cell:

Choose Insert » Name » Define and type CellAbove as the cell name.

In Refers To: type =A1 then click OK.

(Now you can enter the formula =CellAbove into any cell on the sheet - except row one - and it will refer to the cell one row up)

Back to the SUM problem. The formula will now read: =Sum(A1:CellAbove), which means "Sum the cells from A1 to the cell just above me". You can insert rows immediately above the SUM formula, and the relative reference in the named formula causes the SUM to expand automatically.

[ Home ] [ Development ] [ Training ] [ Access Tips ] [ Excel Tips ] [ Philosophy ] [ Take A Break ]

Send mail to dbassist@indigo.ie with questions or comments about this web site.
Copyright © 2002 DBAssist
Last modified: November 03, 2002