insert row not taken into account by sum box

[Steve]

Hi,
Scenario, twelve columns Jan to Dec, rows are for items I have purchased.
I now buy something and need to add a row to name the item.
If I have used the sum tool and I have the formula for my November total
cell L15 as =SUM(L2:L14), if I then need to add a row into my sheet, by
clicking in row L15 and going insert row, row 14 is now for adding my
purchase, and row 15 is now the totals row, the total cell should now read
=SUM(L2:L15) but still reads =SUM(L2:L14), so I have to manually alter the
formula, then edit copy the cell and edit paste it as paste special formula
across all the other columns. This I have to do for each purchase row I add.
I seem to recall the formula used to update and now that a row had been
added, so the cell number after the : changed.
What is going on here ? So annoying
Steve

 

[Pete_UK]

If you were to insert a row within that range, then the range would
adjust automatically. So, if you highlight row 14, for example, and
insert a row, then row 14 would become row 15 (with a new row 14), and
so the formula will automatically adjust.

Rather than do that, though, why not insert a new row 1 and put your
totals above the headings as:

=SUM(L3:L200)

and then you can keep adding data to the bottom without having to
bother about adjusting the formula. Another advantage of this is that
you can freeze panes at A3, so that you can see the totals as you
scroll up and down the sheet, and if you apply filters from the header
row down then the totals can adjust automatically if you use:

=SUBTOTAL(9,L3:L200)

If you do need to adjust all the totals to accommodate more than 200
rows, then you can just highlight row 1 and use Find&Replace (CTRL-H)
to change 200 to whatever.

Hope this helps.

Pete

 

[Gord Dibben]

Either keep a blank row above your SUM formula and insert above that row or
use this SUM fomula in L15 and then just click on L15 and Insert Row as you
are currently doing.

=SUM(L2:INDEX(L:L,ROW()-1))


Gord Dibben MS Excel MVP

 

[Steve]

Hi,
Both good ideas.
I had discovered that by having a blank row above rhe totals row, and
inserting above that keeps it ok, seems odd though that it was designed to
work that way when logic and school days lessons say you have totals row
below the last entry.
Totals at top of columns is an uneasy thing to do with school lessons many
years ago drumming it into us that the total goes underneath, but as
mentioned there could be advantages there with it above.
Steve

 

[AdamV]

I often advocate putting totals at the top, but it takes a change of
mindset.
At school they gave you the numbers, you had to come up with a total so
you added a line for it where there was space - at the bottom. They did
not leave a blank for you.
Also, for old-school accounts (ie paper-based records) and so on it is
often clearer to have an underline then the total at the bottom as it
shows that the total only relates to the numbers above, it, nothing can
be subsequently added. Putting totals at the top leaves the bottom of
the column a bit too open ended. If someone adds a value the total is no
longer valid.

But we live in a new world order where totals are automatically
calculated and updated. The way I think of presenting this sort of
information is that a report should work like a newspaper - headlines
first, then the details. The more detail you need, the more you read.
Only want the headlines? They are at the top, clearly highlighted and if
you don't see anything too unusual / suprising / outrageous you may
not need to read any further.
On this basis, the natural place for totals is always at the top.

The practical reasons for doing it this way to make your life easier are
a nice bonus!

Just a different perspective, I guess.
Adam

PS: have a look at Lists (in Excel 2003) now called Tables (2007) which
will automatically insert rows as needed, move totals down and correct
formulas, and copy formulas down adjacent columns (so lets say in your
example you have a total for each row across the year, or some other
formula in column 13 - it would copy this down the extra row each time too).

 

[Steve]

Thanks,
A very memorable way of looking at it.
I have a lot with totals at base so it will create a mix of sheet designs to
go for top totals.
2007 sounds useful.,
Steve

 

[AdamV]

Glad to help.
Tables in 2007 are called Lists in 2003 and have similar functionality
(although now better in some respects, and easier to format consistently).

Adsam