Auto-update formula to include new cells?

[Ed Henninger]

Hello.

I would like to set up my excel spreadheets so they will automatically
update the sum when I include new cells. I'm sure Excel provides a method to
do this but I have not been able to find reference to it in help files.

Thanks to all for your help on this.

Ed

 

[JE McGimpsey]

Hello.

I would like to set up my excel spreadheets so they will automatically
update the sum when I include new cells. I'm sure Excel provides a method to
do this but I have not been able to find reference to it in help files.

The most efficient method depends a bit on the layout of your data.

If, for instance, the numbers to be summed are all in one column,

=SUM(A:A)

will do.

Other options depend on what you really mean by "include new cells". How
is XL to know what you intend to include?

Another option, would be create a named range. Say, for instance, your
numbers start in B10 and proceed down, and that you don't want to
include anything in B1:B9. You could define a name (Insert/Name/Define):

Names in workbook: myrange

Refers to: =OFFSET(Sheet1!$B$10,,,COUNTA(Sheet1!$B$10:$B$10000),1)

You can then use =SUM(myrange), and number you add to the bottom of the
list will be included.

If that's not what you're looking for, post back with more explanation.