Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Row insert fails to update Sum formula
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... |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Row insert fails to update Sum formula
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Row insert fails to update Sum formula
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Row insert fails to update Sum formula
Nothing 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...row.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" 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 behavior 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Row insert fails to update Sum formula
Thanks 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: Nothing 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...row.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" 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 behavior 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Row insert fails to update Sum formula
If 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" wrote in message ... Thanks 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: Nothing 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...row.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" 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 behavior 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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Row insert fails to update Sum formula
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
insert objects in .xlsx fails with message "cannot insert object" | Excel Discussion (Misc queries) | |||
SECURITY UPDATE FAILS TO INSTALL | Excel Discussion (Misc queries) | |||
MSN Stock Quotes Addin fails to update | Excel Worksheet Functions | |||
Excel formula fails to update | Excel Discussion (Misc queries) | |||
Update fails. Source not found. | Links and Linking in Excel |