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.)




quartz wrote:

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.

"Dave Peterson" wrote:

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


quartz wrote:

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...

--

Dave Peterson


--

Dave Peterson