Insert row and update formulas

[davedave24]

Hello

I have a spreadsheet with several columns, and the row at the bottom of each column is the total (for example, =SUM(A1:A10) )

When I insert a new row under the final line (above the Total row), it should update the Total row to include that new row, but doesn't - the formula stays at A1:A10.

Is there a way around this?

 

[davedave24]

Nevermind guys I found this:

=SUM(A1:INDEX(A:A,ROW()-1))

 

[SkipVought]

hi,

BTW, what version of Excel are you using?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[dsellos]

Hey

Try this:

Aux = Range("A65536").End(xlUp).Row
ActiveCell.FormulaR1C1 = "=SUM(R[1]C:R[" & Aux & "]C)

 

[AnotherHiggins]

Please answer Skip's question. I'm pretty sure I know where he's going with it and you might be making it much harder than it needs to be.

[tt][blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[davedave24]

I'm using 07.

 

[SkipVought]

Then check out the Insert > Table feature that has a Table Tools > Design TAB wher you can do automatic column aggregations, WITHOUT having to perform the VBA as above.

However, I NEVER use column totals at the BOTTOM of a table. I personally DETEST such placement! 1) you make the user HUNT for the aggregation 2) you cannot reliable use other DATA SELECTION AGGREGATIONS as displayed in the Status Bar. I WAY prefer putting aggregations AT THE TOP, ABOVE & ISOLATED from the table, where they are easily seen and do not interfere with other DATA SELECTION AGGREGATIONS.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}