Table query

  • Thread starter Thread starter Brian Tozer
  • Start date Start date
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
 
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)
 
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
 
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.
 
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
 
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.
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
 
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
 
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
 
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
 
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



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
 
Back
Top