Table query

B

Brian Tozer

Assume that I have a data entry table where the last column displays each
row total.
After the table there are several rows that display a Grand Total and
analysis on the table entries.
How would I add extra data entry rows to the table without having to make
any changes to the existing formulas?

Thanks
Brian Tozer
 
B

Bob Phillips

Brian,

Simplest way is to insert a blank row above the totals, set the formula to
span into that blank row, but always insert the new rows above that blank
row and the formulae will remain okay.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
K

Kevin Stecyk

Brian,

You should not post your message in several sections, for that is poor form.

You have responses from Linda, Bob, and myself now. All our solutions are
similar. You should have more than enough information to work with.
Sticking to one thread saves everyone effort.

Kevin
 
D

David McRitchie

Hi Brian and Bob,
The simplest way is to use a macro. You will have to change
your formulas to use OFFSET so that you can insert a new
line immediately after the last row before the Total. Then you
won't have to manually change the formula on the Total row
with subsequent insertions/deletions. This is
a bit different than inserting a row before the total. The idea
is to insert a row in the same format as the previous row and
retain it's formulas.

Insert a Row using a Macro to maintain formulas
http://www.mvps.org/dmcritchie/excel/insrtrow.htm

instructions to install and use a macro can be found in
http://www.mvps.org/dmcritchie/excel/getstarted.htm
and a link to that page or my formula.htm page is at the
top of most of my web pages with macros.
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm
 
B

Brian Tozer

Bob said:
Brian,

Simplest way is to insert a blank row above the totals, set the
formula to span into that blank row, but always insert the new rows
above that blank row and the formulae will remain okay.
Click to expand...

Thanks Bob, but what do you mean by "set the formula to span into that blank
row"?
How do I do this?

Thanks
Brian Tozer
 
B

Brian Tozer

David said:
Hi Brian and Bob,
The simplest way is to use a macro. You will have to change
your formulas to use OFFSET so that you can insert a new
line immediately after the last row before the Total. Then you
won't have to manually change the formula on the Total row
with subsequent insertions/deletions. This is
a bit different than inserting a row before the total. The idea
is to insert a row in the same format as the previous row and
retain it's formulas.

Insert a Row using a Macro to maintain formulas
http://www.mvps.org/dmcritchie/excel/insrtrow.htm

instructions to install and use a macro can be found in
http://www.mvps.org/dmcritchie/excel/getstarted.htm
and a link to that page or my formula.htm page is at the
top of most of my web pages with macros.
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm
Click to expand...

Thanks David.
I am finding that what appears to be a trivial requirement is not as simple
as I thought.
Either all the replies that I have received so far have not actually been
tried, or I am inordinately stupid.
I was suspecting the latter.
Brian Tozer
 
K

Kevin Stecyk

A B C
1 47 38 =A4+B4
2 53 15 =A5+B5
3 80 34 =A6+B6
4 70 83 =A7+B7
5 53 70 =A8+B8
6 67 81 =A9+B9
7 56 10 =A10+B10
8 =A11+B11
9 =SUM(C1:C8)



In my earlier example to you, I did not leave a blank row. Thus, when you inserted additional rows using the "sum row", the formula was incorrect. Previously I simply advised correcting the formula. Bob simply has you leaving a blank row like shown above.

So now you can select Row 8 to add a row, Row 9 will still show the correct formula. You must maintain one blank row after your data for Bob's solution to work.

As you discovered in our prior discussion, if you insert a row by selecting Row 9, the formula is incorrect.

Regards,
Kevin
 
K

Kevin Stecyk

A B C
1 47 38 =A1+B1
2 53 15 =A2+B2
3 80 34 =A3+B3
4 70 83 =A4+B4
5 53 70 =A5+B5
6 67 81 =A6+B6
7 56 10 =A7+B7
8 =A8+B8
9 =SUM(C1:C8)



A B C
1 47 38 =A4+B4
2 53 15 =A5+B5
3 80 34 =A6+B6
4 70 83 =A7+B7
5 53 70 =A8+B8
6 67 81 =A9+B9
7 56 10 =A10+B10
8 =A11+B11
9 =SUM(C1:C8)



In my earlier example to you, I did not leave a blank row. Thus, when you inserted additional rows using the "sum row", the formula was incorrect. Previously I simply advised correcting the formula. Bob simply has you leaving a blank row like shown above.

So now you can select Row 8 to add a row, Row 9 will still show the correct formula. You must maintain one blank row after your data for Bob's solution to work.

As you discovered in our prior discussion, if you insert a row by selecting Row 9, the formula is incorrect.

Regards,
Kevin
 
B

Brian Tozer

A B C
1 47 38 =A1+B1
2 53 15 =A2+B2
3 80 34 =A3+B3
4 70 83 =A4+B4
5 53 70 =A5+B5
6 67 81 =A6+B6
7 56 10 =A7+B7
8 =A8+B8
9 =SUM(C1:C8)


In my earlier example to you, I did not leave a blank row. Thus, when you inserted additional rows using the "sum row", the formula was incorrect. Previously I simply advised correcting the formula. Bob simply has you leaving a blank row like shown above.

So now you can select Row 8 to add a row, Row 9 will still show the correct formula. You must maintain one blank row after your data for Bob's solution to work.

As you discovered in our prior discussion, if you insert a row by selecting Row 9, the formula is incorrect.

Regards,
Kevin

Thankyou thankyou thankyou Kevin.
I now see what you mean and have it working perfectly.
Merci for your patience with me.

Brian Tozer
 
D

David McRitchie

You won't need to modify the formula later, nor would you have
to maintain an empty row before the total if you used OFFSET.

Instead of
C9: =SUM(C1:C8)
use:
C9: =SUM(C1:OFFSET(C9,-1,0)

more information on my page:
Inserting Rows and Maintaining Formulas
: http://www.mvps.org/dmcritchie/excel/insrtrow.htm
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm


A B C
1 47 38 =A1+B1
2 53 15 =A2+B2
3 80 34 =A3+B3
4 70 83 =A4+B4
5 53 70 =A5+B5
6 67 81 =A6+B6
7 56 10 =A7+B7
8 =A8+B8
9 =SUM(C1:C8)


In my earlier example to you, I did not leave a blank row. Thus, when you inserted additional rows using the "sum row", the
formula was incorrect. Previously I simply advised correcting the formula. Bob simply has you leaving a blank row like shown above.

So now you can select Row 8 to add a row, Row 9 will still show the correct formula. You must maintain one blank row after your
data for Bob's solution to work.

As you discovered in our prior discussion, if you insert a row by selecting Row 9, the formula is incorrect.

Regards,
Kevin

Thankyou thankyou thankyou Kevin.
I now see what you mean and have it working perfectly.
Merci for your patience with me.

Brian Tozer
 
Top