Microsoft Microsoft office excel 2007 ac...

In Excel 2007, when ever a cell is inserted in between the last of the range & the summation formula, the formula doesn't automatically takes the newly inserted cell in calculation instead an from error checking i have to select "Update formula to include cells" But cell if inserted anywhere in between the range then the summation formula takes it into account.

Pl help to sort this error.

Regards, Nishith

It's not an "error", it's how Excel works. Excel assumes that if you use =SUM(A1:A5) then you want to start with the value in A1 and end with the value in A5.

If you insert a value in Row 3, Excel assumes that you still want the last value, which is now in A6 so it updates the formula.

However, if you add a row

belowA5, Excel maintains the orignal assumption: The value in A5 is still the last value that you want included.The workaround is to leave a blank row between the last value and the SUM formula and include that blank row in the SUM formula. That way, when you insert a value just below the last value, it will be included in the formula.

P.S. I run into that problem all the time with client worksheets when I add new rows below the last value. Instead of editing the SUM formula itself, I select the cell when the SUM formula is and click a button on my toolbar which runs the macro below.

When the InputBox is presented, I simply select the entire range that I want to SUM and click OK.

I don't have to touch the keyboard or deal with editing the formula. It's 3 clicks and a mouse drag.

I also use the macro to set up new spreadsheets for new clients since it not only inserts the formula but formats the cell as well.

Sub SumAccount() 'Sum Range and Format Cells On Error GoTo done Set MyRange = Application.InputBox("Select Cells To Sum", Type:=8) With ActiveCell .Formula = "=SUM(" & MyRange.Address & ")" .Font.Size = 8 .Font.Bold = False .NumberFormat = "#,##0" .HorizontalAlignment = xlRight End With done: End SubIt fits in with my practice of "If I'm going to repeat the same task over and over again, it's proably more efficient to write a macro." The payback period for the time spent on the macro is usually pretty short for tasks that get repeated hundreds of times.

Ask Your Question

Weekly Poll

Have you played Jackbox during the pandemic?

Discuss in The Lounge

Poll History