Excel >> Row insert fails to update Sum formula

by cXVhcnR6 » Mon, 04 Apr 2005 00:43:02 GMT

I am using Office 2003.

A new row is inserted in a sheet programmatically. When this is done the sum
formulas (that have also been entered programmatically) do not update to
include the row inserted, unless a value is entered on the newly inserted
row.

NOTE: This only occurs when the row inserted causes the row containing the
sum formulas to shift down. For example, row 14 contains sum formulas. My
code inserts a row on row 14. The sum formula, shifted to row 15, now
excludes row 14.

If a value is entered into row 14, then the sum formula in that column
sometimes updates to include row 14, but sometimes it doesn't update (don't
know why).

The sheet has split windows on and some cells are locked, but my function
unprotects the sheet while inserting the row.

How can I correct this behaviour programmatically so that the formulas will
include all rows to begin with [e.g. =SUM(A4:A18)] ?

Thanks in advance for your assistance. I hope my description is
understandable...

Excel >> Row insert fails to update Sum formula

by Dave Peterson » Mon, 04 Apr 2005 01:09:24 GMT


Maybe you can just change the formula.

If the =sum() formula goes in A19, then something like this:
=SUM(A4:OFFSET(A19,-1,0))
should work.

And take a look at what happens when you turn on (or off):
Tools|options|edit tab
Extend data range formats and formulas




--

Dave Peterson

Excel >> Row insert fails to update Sum formula

by cXVhcnR6 » Mon, 04 Apr 2005 01:47:02 GMT

Thanks Dave, but wasn't this sort of thing a non-issue in earlier versions of
Excel? Is Microsoft moving backwards?

I worked out code to update the formulas, it's just too bad I have to code
something that should be automatic...

Thanks also for the tip about "Extend data range" this doesn't have any
bearing on my issue.

Excel >> Row insert fails to update Sum formula

by David McRitchie » Mon, 04 Apr 2005 02:38:01 GMT

othing would have changed in this regard from earlier versions, or in later
versions of Excel (well when something later than your Excel 2003 comes out).

There is a better description of the problem and the reason why you want to
refer to cell that the formula has been moved to and then offset back one
row on my page http://www.mvps.org/dmcritchie/excel/offset.htm

If you have a formula such as in

A12: 100
A13: 50
A14: =SUM(A$2:A13)

if you insert a row before row 13 the reference on A15 gets changed
to A14 because the cell that was A13 is now A14 - no problem.

If you insert a row before row 14 the reference on A15 still refers
to the same cell on A13 -- you've got a problem.

If you change the original formula as Dave Peterson indicated to
A14: =SUM(A$2:OFFSET(A14,-1,0)
you will see that A14 gets change to A15 same row as the formula
as before and the OFFSET to the row before works same as before.

You might want to look at both of these pages:
Using OFFSET to maintain formulas
http://www.mvps.org/dmcritchie/excel/offset.htm

Insert a Row using a Macro to maintain formulas
Why must we use that funny looking OFFSET Worksheet Function (#offset)
http://www.mvps.org/dmcritchie/excel/insrtrow.htm#offset

In fact you probably want to look at the entire insrtrow page
because you indicated you were doing things programmatically

Please use your first and last name when posting, it makes it a lot easier
and more friendly for everyone concerned.
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"quartz" < XXXX@XXXXX.COM > wrote ...




Excel >> Row insert fails to update Sum formula

by cXVhcnR6 » Mon, 04 Apr 2005 03:41:02 GMT

hanks much David. I just don't remember ever having had this issue before
and I've done a LOT of coding.

I bookmarked your web page. Thanks again.

"David McRitchie" wrote:


Excel >> Row insert fails to update Sum formula

by Tom Ogilvy » Mon, 04 Apr 2005 05:37:40 GMT

f you insert within the range, you should get an adjusted formula. It
sounds like you are inserting outside the range. Maybe you haven't done
that before or are misremembering.

--
Regards,
Tom Ogilvy

"quartz" < XXXX@XXXXX.COM > wrote in message
news: XXXX@XXXXX.COM ...
later
out).
to
one
(#offset)
easier
versions of
code
any
done the sum
update to
inserted
containing the
formulas. My
now
column
update (don't
function
formulas will



Excel >> Row insert fails to update Sum formula

by Dave Peterson » Mon, 04 Apr 2005 05:59:26 GMT

I'm not so sure it didn't have anything to do with your situation.

I put 1, 2, 3 in A1:A3 with that option toggled off.
I put =sum(a1:a3) in A4.
I inserted a new row #4 and the formula in A5 remained =sum(a1:a3).

Then I deleted row 4.
I toggled that setting on (extend the formulas)
I still had =sum(a1:a3) in A4.
I inserted a new row 4.
The formula in A5 still showed: =sum(a1:a3)
I typed something into A4 and the formula in A5 changed to: =sum(a1:a4)

This option was added in xl2002. (I keep it turned off for me. I don't like it
either.)








--

Dave Peterson

Similar Threads

1. Automatically update formula (sum) when inserting row

I have very simple spreadsheet with 5 columns.  Last cell in each column is 
sum of cells above it using =SUM.  I thought the formula would update 
automatically when I insert rows so that the new row cell would be added to 
the sum but this isn't happening.  Automatic calculation is checked.  What 
else do I need to do?

2. Insert row above Sum - update formula - Excel

3. vba insert row does not update Sum formula

Hi,

I have code that inserts a row for additional data that needs to be summed. 
But the sum formula below this new row does not update to include the newly 
increased number of rows.

The only thing I have found in the forums is someone's suggestion to always 
leave a blank row just above the sum formula, and insert new rows above it.

I have not tried this because it seems most inelegant!

It seems there must be a way this can be done.

Thanks for any insights you may have.

Harold 


4. Adding a new row to a worksheet and having the sum formula automaticly update???Help - Excel Functions&Formulas

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

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

6. how do you get a formula to not update when rows are inserted - Excel

7. cell formula not updating right after insert row

Hello,

I have a formula in a column as follows - the details of what it does are for 
the most part unimportant (this one is at row 50):
   =IF(D50<>"",D50-SUM(G50:AA50), IF(C50<>"",  E49  +C50-SUM(G50:AA50),""))

I frequently end up inserting rows and copying the above formula to the right 
column for the new row. What happens though is that this formula and all those 
following get messed up as so:
   =IF(D51<>"",D51-SUM(G51:AA51), IF(C51<>"",  E49  +C51-SUM(G51:AA51),""))

Everything got adjusted except for the reference to E49 which should have 
become E50 (in the new row). I presume that it tries to keep references to 
where they were before that this happens but it messes me up in this instance 
as current rows need to know the immediately prior row and not earlier (like a 
chain).  Is there anything anyone can recommend to me regarding this?

It is actually a part of a spreadsheet that helps me plan the budget for my 
home where I can either specify how much is in my account or how much I expect 
to see deposited (in which case the amount is added to the prior effective 
total - that's where the e49 came in). I can then specify different bills for 
that time on the same row to see what or when I can afford to pay them.

Thanks,
Ben

8. updating formulas at inserted rows with locked columns - Excel