Autosum after Insert Row

Insomniac53 · 07-05-2011, 04:48 AM

I only use Excel when I really have to so excuse this question if it's missing some basic understanding. I can get into VBA and macros if necessary, but it hurts. The actual workbook is much more complicated than the following simplified problem, but the principle is the same:

A column has 11 rows of numbers and in the 11th row is an autosum =SUM(A1:A10). Standard stuff. However, when a new row is inserted at row 11, the new 12th row should now read =SUM(A1:A11) but it remains as it was, to A10.

I would have thought it logical for Excel to update the row count automatically, but it doesn't. Is there a simple way to do this or using VBA if absolutely necessary?

Thanks.

**TMS** · 07-05-2011, 05:06 AM

You could try:

=SUM(A1:OFFSET(A12,-1,0))

Oddly enough, I've just tested this in Excel 2007 and when I insert the row and then put a number in, the formula that was in A11 and is now in A12 does automatically adjust.

You might find that it's an Excel setting that automatically adjusts the formula in these circumstances but that's just a guess.

Anyway, the OFFSET formula will work regardless.

Regards

Insomniac53 · 07-05-2011, 05:22 AM

Excellent! Works a treat. Many thanks.
As a point of interest, there are four other columns with similar sums. Three use autosum and have (or rather 'had') the same issue. The fourth column looks across the 'totals' rows and adds them, something like =A11+B11+C11, and this one updates correctly to =A12+B12+C12 when a row is inserted. I shall look for a setting, but probably not too hard as your answer solves the problem.
Thanks again!

**arthurbr** · 07-05-2011, 05:38 AM

In 2003 : Tools - Options - Edit - "extend data range..." should be checked to achieve this ( formula adapts when value entered in inserted cell)

Insomniac53 · 07-05-2011, 05:50 AM

Thanks Arthurbr. I'm using 2010 so the menu route is different. I had a quick look at Excel Options but haven't seen anything similar. Maybe someone with 2010 can tell us the setting, if there is one?