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

[tbone]

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]

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]

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)

 

[Alain]

You can define the name THIS as follow:

Assuming the Active Cell is C21, go in Define Name,
Name: This
Scope: Workbook
Refers to: =!C21

The formula: =SUM(C4:OFFSET(THIS,-1,0)) will then work as expected

Alain

 

[tbone]

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)

What I was looking for was something more generic. If I define the
name "THIS", there's only one "THIS" on the sheet. If I have a table
with 10 columns, all of which are summing or counting, I'd need a
"THIS", "THAT", and "THEOTHERTHING"...

What I was looking for was a more generic, or perhaps dynamic, way of
referring to the current cell - a self-reference that works wherever
its needed. With that as the anchor, then OFFSET and other functions
can be used to achieve the desired result. Too bad OFFSET doesn't let
you specify RC for the cell reference.

In the meantime, I've returned to using the tiny row method to ensure
that rows are always inserted within the range of the formulas that
use them.

But still, it's an interesting exercise to me to think about cleverer
ways to address this.

Thanks
tbone

 

[RagDyer]

You might try using the "Extend" feature.

From the Menu Bar:
<Tools> <Options> <Edit> tab, and check:
"Extend List Formats And Formulas".

Now, when you select the row containing your Sum() formula,
and Insert a new row (which is inserted right above the formula row),
You'll see the formula change to include that newly inserted row.

This is from my XL 2K.
Newer versions make use of a "List" feature which produces the same type of
results.

 

[Alain]

I don't know if you looked at my reply but it do exactly what you are asking
for from any column in any sheet...

 

[tbone]

I don't know if you looked at my reply but it do exactly what you are asking
for from any column in any sheet...

I did look at your reply..

You can define the name THIS as follow:

Assuming the Active Cell is C21, go in Define Name,
Name: This
Scope: Workbook
Refers to: =!C21

The formula: =SUM(C4:OFFSET(THIS,-1,0)) will then work as expected

....but if I understand your meaning, the "THIS" in your example will
work for only one summed column on a sheet. If I have several, each
one would need a separate and distinct "THIS" named range to refer to
the cell in the corresponding column in the row above.

What I'm really looking for is a cell self-reference that could be
used multiple times in a given sheet; i.e THIS means "this cell",
wherever it happens to be, separate and distinct from "THIS" in any
other cell in the workbook.

.... or "SELF" or "ME" or "HERE" or something...

That way, a formula like that could be copy/pasted; Excel would do its
relative adjustment magic, and the meaning would remain.

Am I missing what you're trying to convey? I guess I'm not doing a
very good job of explaining myself. But thanks for the suggestion. It
keeps me thinking.

Thanks
tbone

 

[tbone]

You might try using the "Extend" feature.

From the Menu Bar:
<Tools> <Options> <Edit> tab, and check:
"Extend List Formats And Formulas".

Now, when you select the row containing your Sum() formula,
and Insert a new row (which is inserted right above the formula row),
You'll see the formula change to include that newly inserted row.

This is from my XL 2K.
Newer versions make use of a "List" feature which produces the same type of
results.

My Excel 2003 version already has the "Extend..." checkbox checked.

I did play with the List construct a bit (now I see how people get
that automatic sorting stuff from). It's quite different from what I'm
used to, but it does look interesting. It can handle column headers
and footers, so it knows where the list really ends.

I will keep Lists in mind for future spreadsheets.

Thanks for that pointer!
tbone

 

[Dave Peterson]

Did you try it?

Or even better, did you try using it in formulas in different cells?

 

[Alain]

I understand exactly what you want and it do exactly that, no need to define
other names since THIS would always refers to the active cell from any
sheet...
I don't know if you are familiar with the difference between absolute and
relative addresses but when you use relative addresses in defined names they
behaves the same way as in a cell formula so they are updated dynamically.
So if you define the name THIS with a reference to the current active cell
it will always reference the current active cell and putting a single ! at
the beginning of the address will even make it work from any sheet... Just
try it and you will see...

 

[tbone]

I didn't realize that named ranges could do relative as well as
absolute references! So I didn't try it! I thought that the named
range editor just always used the "$" as a matter of course. That,
plus the editing of the range is a little tricky since you can't use
the arrow keys to move around - the mouse is required.

But it works! Groovy!

Thanks
tbone

 

[tbone]

I had no clue about named relative cell references. That's exactly
what I'm looking for. I didn't read your earlier response carefully
enough to notice the difference.

I was also pleased to see that when inspecting the names, the cell
reference is shown relative to the currently active cell.

Thanks for your patience in pointing this out. This opens a whole new
set of possibilities, e.g.:

CellAbove
This

Merci beaucoups!
tbone

 

[Gord Dibben]

You don't have to use the mouse if you hit F2 to get into edit mode while in
the "refers to" dialog.

Arrow keys will move you through.


Gord Dibben MS Excel MVP

 

[tbone]

Thanks Gord! It didn't occur to me to try that, even though I often
use F2 when editing in a cell.

I appreciate the pointer!
tbone