Results 1 to 15 of 25

20090211, 08:10 #1
 Join Date
 Dec 2001
 Location
 Melbourne, Australia
 Posts
 4,594
 Thanks
 0
 Thanked 27 Times in 27 Posts
Summing rows then inserting rows (Access 2003 SP2)
I have a task where there is a formula in A11 that sums A4 thru A10.
When i insert row(s) after A10 do I need to change the formula that used to be in A10? I presume i do, can this be done?

20090211, 08:21 #2
 Join Date
 Nov 2001
 Posts
 10,550
 Thanks
 0
 Thanked 7 Times in 7 Posts
Re: Summing rows then inserting rows (Access 2003 SP2)
Patt,
It would probably have been quicker to test this than to post the question.
If you have relative cell references in A11  for example =SUM(A4:A10)  then this will update when you insert the row
If you have absolute cell references in A11  for example =SUM(A$4:A$10)  then this will not update when you insert the row
StuartR

20090211, 08:46 #3
 Join Date
 Dec 2001
 Location
 Melbourne, Australia
 Posts
 4,594
 Thanks
 0
 Thanked 27 Times in 27 Posts
Re: Summing rows then inserting rows (Access 2003 SP2)
Thanks Stuart, I will test this at work tomorrow.
Do the $ signs indicate a fixed range?

20090211, 09:19 #4
 Join Date
 Jan 2001
 Location
 Chelsea, Gtr London, United Kingdom
 Posts
 587
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Summing rows then inserting rows (Access 2003 SP2)
The formula is in A11? (I assume a typo)
If you insert a row within a range the range expands to accomodate it. If you insert a row between A10 and A11 (i.e. at the end of the range) the range does not expand to accomodate it  you would need to adjust your formula in this case.Steve H
IT Lecturer/Access Developer
O2K SR3/O2010; Win7Pro

20090211, 09:31 #5
 Join Date
 Nov 2001
 Posts
 10,550
 Thanks
 0
 Thanked 7 Times in 7 Posts

20090211, 09:35 #6
 Join Date
 Nov 2001
 Posts
 10,550
 Thanks
 0
 Thanked 7 Times in 7 Posts
Re: Summing rows then inserting rows (Access 2003 SP2)
Steve,
> If you insert a row between A10 and A11 (i.e. at the end of the range) the range does not expand to accomodate it
That's what I thought, but when I tested it just now (Excel 2003 SP1) it behaved as I describe. Maybe this is a difference between inserting the reference interactively and inserting it from code?
The strange thing is that after I inserted the row the formula was still =SUM(A4:A10), but when I inserted a value into the new A11, the formula in the cell that was now A12 immediately changed to =SUM(A4:A11).
StuartR

20090211, 10:23 #7
 Join Date
 Jan 2001
 Location
 Chelsea, Gtr London, United Kingdom
 Posts
 587
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Summing rows then inserting rows (Access 2003 SP2)
I guess the inserting of the value caused Excel's 'I'll b*gg*r about with your spreadsheet trying to be helpful' feature to kick in.
Suffice to say, I would not rely on it to do that every time. In answer to Patt's original post, one reliable solution is to leave a blank row between the table and the total but include the blank row in the formula. When you insert a row immediately after the last data row you will be inserting it within the range and things should be adjusted accordingly.Steve H
IT Lecturer/Access Developer
O2K SR3/O2010; Win7Pro

20090211, 10:28 #8
 Join Date
 Nov 2001
 Posts
 10,550
 Thanks
 0
 Thanked 7 Times in 7 Posts
Re: Summing rows then inserting rows (Access 2003 SP2)
> Excel's 'I'll b*gg*r about with your spreadsheet trying to be helpful' feature
<img src=/S/rofl.gif border=0 alt=rofl width=15 height=15>

20090211, 17:18 #9
 Join Date
 Dec 2001
 Location
 Melbourne, Australia
 Posts
 4,594
 Thanks
 0
 Thanked 27 Times in 27 Posts
Re: Summing rows then inserting rows (Access 2003 SP2)
Thank you for your comments gents, it should be interesting to test it and see what happens.

20090211, 18:58 #10
 Join Date
 Nov 2002
 Location
 New York, New York, USA
 Posts
 264
 Thanks
 0
 Thanked 17 Times in 17 Posts
Re: Summing rows then inserting rows (Access 2003 SP2)
Some alternate solutions:
1. When I encounter similar situations I try to set up the worksheet so that the Total is at the top of the range rather than at the bottom then a simple sum formula for example in Range A1 of Sum(A1:A100) would allow for future insertion of rows and your Total would yield the proper results
2. A more complex solution is to put the final total in a lower row say for example row 101 as Sum(A1:A100), then in your example at the start hide rows 11 to 100. Whenever you add rows just make sure to unhide the new rows. Again your Total should yield the proper results.
Regards,
Regards,
Tom Duthie

20090211, 20:53 #11
 Join Date
 Dec 2001
 Location
 Melbourne, Australia
 Posts
 4,594
 Thanks
 0
 Thanked 27 Times in 27 Posts
Re: Summing rows then inserting rows (Access 2003 SP2)
I cannot do 1 above as the client wants the total at the bottom.
I need to have the total in A11 since if no inserts are required as will usually be the case it stays in the same position.
I tried putting =Sum(A4:A10) in A11 and ended up inserting about 80 rows, the total ended up being A4 thru A10, it doesn't take into account the additional rows.

20090211, 21:06 #12
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 28 Times in 28 Posts
Re: Summing rows then inserting rows (Access 2003 SP2)
Where did you insert the rows?

20090211, 22:33 #13
 Join Date
 Dec 2001
 Location
 Melbourne, Australia
 Posts
 4,594
 Thanks
 0
 Thanked 27 Times in 27 Posts
Re: Summing rows then inserting rows (Access 2003 SP2)
I programmatically populate rows 4 onwards, if i get to row 11 i then do an insert and populate row 11, the total row being pushed down to row 12, etc etc

20090211, 22:56 #14
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 28 Times in 28 Posts
Re: Summing rows then inserting rows (Access 2003 SP2)
You should leave one row between the data and the total row blank, but include it in the sum formula.
E.g.: totals in row 11.
Data in rows 49.
Row 10 is blank, but the sum formulas look like =SUM(A4:A10)
When you need a new row, insert it in row 10, and fill the new row 10.
The original empty row 10 is pushed down to row 11 (and the totals to row 12), and the formula is automatically adjusted to =SUM(A4:A11) without you having to change it.

20090211, 23:54 #15
 Join Date
 Dec 2001
 Location
 Melbourne, Australia
 Posts
 4,594
 Thanks
 0
 Thanked 27 Times in 27 Posts
Re: Summing rows then inserting rows (Access 2003 SP2)
Clever trick, does that mean I will have a blank row at the end of that column before the Total row?
If so, can i just delete that row?
Thank you for your help.