Is there a way to make the sum function's range auto update when rows are
added above it. An example would be:

I have the following formula in K20, =SUM(K15:K19). If row 20 is selected
and a new row is inserted, the formula still sums K15:K19. I would like the
formula to update to =SUM(K15:K20) after the row is inserted.

Thanks
Dave

What you're describing sounds like the ideal situation for using a Dynamic
Range Name. They automatically expand and contract with data additions and
deletions, respectively.

Check Debra Dalgleish's Contextures website for instructions on how to
create and use them:

http://www.contextures.com/xlNames01.html#Dynamic

Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro


"Dave" wrote:

Is there a way to make the sum function's range auto update when rows are
added above it. An example would be:

I have the following formula in K20, =SUM(K15:K19). If row 20 is selected
and a new row is inserted, the formula still sums K15:K19. I would like the
formula to update to =SUM(K15:K20) after the row is inserted.

Thanks
Dave

Another alternative:

K20: =SUM($K$15:OFFSET(K20,-1,0))
That formula ALWAYS sums from K20 through the cell immediately above the
cell the SUM function is in.

Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro


"Ron Coderre" wrote:

What you're describing sounds like the ideal situation for using a Dynamic
Range Name. They automatically expand and contract with data additions and
deletions, respectively.

Check Debra Dalgleish's Contextures website for instructions on how to
create and use them:

http://www.contextures.com/xlNames01.html#Dynamic

Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro


"Dave" wrote:

Is there a way to make the sum function's range auto update when rows are
added above it. An example would be:

I have the following formula in K20, =SUM(K15:K19). If row 20 is selected
and a new row is inserted, the formula still sums K15:K19. I would like the
formula to update to =SUM(K15:K20) after the row is inserted.

Thanks
Dave

We need something like this as well. We are always inserting rows and need the formulas to include new rows.

The only way I know around this is to select a row with in the range and insert. For example, select row 19 and insert there. If
you need the data that was in row 19 (which is now in row 20 and 19 is blank) to stay in that row, then you would have to sort the
data, copy and paste, or cut and insert.

Our new data will include all of the old data and the new rows. So what we normally do is copy all rows of the new data and paste
it over the top of the old data, replacing the whole set. The last row falls right into place right where it should be.

HTH,

Conan



"Dave" wrote in message ...
Is there a way to make the sum function's range auto update when rows are
added above it. An example would be:

I have the following formula in K20, =SUM(K15:K19). If row 20 is selected
and a new row is inserted, the formula still sums K15:K19. I would like the
formula to update to =SUM(K15:K20) after the row is inserted.

Thanks
Dave