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

--
Running Excel 2000 SP-3 on Windows 2000

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

--
Hope this helps
Martin Fishlock, Bangkok, Thailand
Please do not forget to rate this reply.


"LurfysMa" wrote:

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

--
Running Excel 2000 SP-3 on Windows 2000

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

--
Hope this helps
Martin Fishlock, Bangkok, Thailand
Please do not forget to rate this reply.


"LurfysMa" wrote:

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

--
Running Excel 2000 SP-3 on Windows 2000

On Sat, 24 Feb 2007 19:19:00 -0800, Martin Fishlock
wrote:

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

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.

--
Running Excel 2000 SP-3 on Windows 2000

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.
--
Hope this helps
Martin Fishlock, Bangkok, Thailand
Please do not forget to rate this reply.


"LurfysMa" wrote:

On Sat, 24 Feb 2007 19:19:00 -0800, Martin Fishlock
wrote:

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

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.

--
Running Excel 2000 SP-3 on Windows 2000