Re: need to have SUM formula update on row add/delete/move - Excel 2003

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

You could write the formula in C21 as:

=SUM(C4:OFFSET(c21,-1,0))

And it would adjust when you inserted a new cell/row.

But you could also create a name (insert|name|define) named This.

Select A1
In xl2003 menus:
Insert|Name|Define
Names in Workbook: This
Refers to: A1

Then you could use:
=SUM(C4:OFFSET(THIS,-1,0))

If you really inventive, you could have selected A2:
Insert|Name|Define
Names in Workbook: CellAbove
Refers to: A1

and use:
=sum(c4:cellabove)



tbone wrote:

Say I have a table with a column that is summed. The formula would be
something like:

=SUM(C4:C20)

Assume there are column heads above this range, and that this formula
is in C21. I'd imagine this is the most common arrangement used.

If I insert a new row above 21, 99.9% of the time, I want the SUM to
be updated to include that row. That does happens *UNLESS* I insert
the row just above row 21. Unfortunately, that's where new rows want
to go most of the time.

Even when the table or column is named, it still doesn't expand the
formula to include the new row. Rightfully so I suppose, since the new
row is not logically within the formula's range.

Deleting and moving rows have similar issues.

I know there's an "INDIRECT" function, but it seems like a rather
indirect way to get the desired result:

=SUM(C4:INDIRECT("C"&ROW()-1))

Several things are lost by having to do it this way, e.g.: the
highlighting of range references when editing the cell, and the
automatic adjustment that Excel does when the formula is copied to
another cell.

I have taken to inserting a tiny row just above the row containing the
SUM, so I can insert new rows that look like they're being added to
the end of the range, but in fact the SUM includes the cell in the
tiny row, so I can trick Excel into doing what I want. Of course, to
actual do the insert, I have to try to select that tiny row! :-)

So one alternative that strikes me as useful is a "this cell"
reference, such that I could do something like:

=SUM(C4:OFFSET(THIS,-1,0))

It seems to me this formula really matches what's visually being done.
Is there such a thing (I couldn't find one)? If not, could this be
implemented as a "user formula"?

Is there a better method for adding, deleting, and moving rows that
would allow formulas such as SUM and COUNT to adjust as needed?

Thanks
tbone

--

Dave Peterson
.

Relevant Pages

  • Re: Understanding IncludePicture
    ... into a cell of a table in the header of a document, and then adjust the size ... 'Insert Logo on Title Page ... I've tried inserting a text frame and the includepicture ...
    (microsoft.public.word.vba.general)
  • RE: a twist on a VLOOKUP?
    ... think of it as each cell in your range is evaluated ... "Valerie" wrote: ... adjust it to work. ... spreadsheet for a particular code and then sum the values that would be 4 ...
    (microsoft.public.excel.misc)
  • Re: ABS
    ... ??>> You can't change the value in a cell using a formula. ... ??>> But you can sum the absolute values of a range: ... ??>> Adjust the range to match, but you can't use the whole ... ??>>> How do I change the cells in a column to absolute values ...
    (microsoft.public.excel.misc)
  • RE: a twist on a VLOOKUP?
    ... "Valerie" wrote: ... think of it as each cell in your range is evaluated ... adjust it to work. ... spreadsheet for a particular code and then sum the values that would be 4 ...
    (microsoft.public.excel.misc)
  • Re: countif multiple conditions
    ... if your sheet names or cell locations are different, ... location they don't adjust. ... John word ... Ann excel ...
    (microsoft.public.excel.worksheet.functions)