How to add a row to a table with totals?

  • Thread starter Thread starter LurfysMa
  • Start date Start date
L

LurfysMa

I have a shreadsheet that is made up of sub-tables comprising 3-6 rows
with a header and a totals line. For example:


Row Date Units Minutes U/M Comments
5 1/12/07 50 25 2.0 xxx
6 1/13/07 100 40 2.5 xxx
7 Totals 150 65 2.3 xxx

How can I insert a row between the last data row (6) and the totals
row (7) in such a way that (a) all the forumlas in the inserted row
acquire the attributes of the row above it and (b) the sum formulas in
the total row are updated to include the new row?

If I select row 5, then drag the top border to between rows 5 & 6
using Ctrl+Shift+Drag, row 5 is replicated, adding a new row, and the
totals all update. But the new row is in the wrong place. I want it
just above the totals row.

If I do the same thing, but drag it to between rows 6 & 7, which is
where I want it, the row gets copied, but the totals row is not
updated. What's up with this?

The insert row function has the same problem. If I insert a row above
row 6, the totals get updated, but the new row is in the wrong place.
If I insert it above row 7, the totals are not updated.

The only solution I could come up with is:

1. Select row 5.
2. Copy and insert it between rows 5 & 6 using Ctrl+Shift+Drag to drag
the top border to the bottom border.
3. Move row 6 (now 7) up one row using Shift+Drag.

Is there a 1-step way to insert a row and have the attributes of the
previous row copied and the totals row updated?

Thanks
 
Hi:

one way is to have a blank row between your data and your total
and set the height to 1.

then on your sums include the blank row and then you can insert a row after
the last data row and the sums will still work.

so in your example

Row Date Units Minutes U/M Comments
5 1/12/07 50 25 2.0 xxx
6 1/13/07 100 40 2.5 xxx
7
8 Totals =sum(b5:b7) =sum(c5:c7) =b8/c8 xxx

and then when you insert a row the references will be automatically adjusted:

Row Date Units Minutes U/M Comments
5 1/12/07 50 25 2.0 xxx
6 1/13/07 100 40 2.5 xxx
7 1/14/07 50 25 2.0 xxx
8
9 Totals =sum(b5:b8) =sum(c5:c8) =b9/c9 xxx
 
Hi:

one way is to have a blank row between your data and your total
and set the height to 1.

then on your sums include the blank row and then you can insert a row after
the last data row and the sums will still work.

so in your example

Row Date Units Minutes U/M Comments
5 1/12/07 50 25 2.0 xxx
6 1/13/07 100 40 2.5 xxx
7
8 Totals =sum(b5:b7) =sum(c5:c7) =b8/c8 xxx

and then when you insert a row the references will be automatically adjusted:

Row Date Units Minutes U/M Comments
5 1/12/07 50 25 2.0 xxx
6 1/13/07 100 40 2.5 xxx
7 1/14/07 50 25 2.0 xxx
8
9 Totals =sum(b5:b8) =sum(c5:c8) =b9/c9 xxx
 
Hi:

one way is to have a blank row between your data and your total
and set the height to 1.

then on your sums include the blank row and then you can insert a row after
the last data row and the sums will still work.

so in your example

Row Date Units Minutes U/M Comments
5 1/12/07 50 25 2.0 xxx
6 1/13/07 100 40 2.5 xxx
7
8 Totals =sum(b5:b7) =sum(c5:c7) =b8/c8 xxx

and then when you insert a row the references will be automatically adjusted:

Row Date Units Minutes U/M Comments
5 1/12/07 50 25 2.0 xxx
6 1/13/07 100 40 2.5 xxx
7 1/14/07 50 25 2.0 xxx
8
9 Totals =sum(b5:b8) =sum(c5:c8) =b9/c9 xxx
Click to expand...

That works. It's a reasonable work-around.

I would think that there ought to be a straightforward way to add a
row to a table with sums and have the sums automatically update.

Thanks for the help.
 
I came across another solution yesterday on Contextures site.

=sum(b5:offset(b7,-1,0))

and this will adjust the rows for inserted rows without the blank.
 
Back
Top