# Thread: Summing rows then inserting rows (Access 2003 SP2)

1. ## 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?

2. ## 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

3. ## 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?

4. ## 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.

5. ## Re: Summing rows then inserting rows (Access 2003 SP2)

> Do the \$ signs indicate a fixed range?

You can read more about Absolute and Relative references in Excel here.

StuartR

6. ## 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

7. ## 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.

8. ## Re: Summing rows then inserting rows (Access 2003 SP2)

<img src=/S/rofl.gif border=0 alt=rofl width=15 height=15>

9. ## 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.

10. ## 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

11. ## 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.

12. ## Re: Summing rows then inserting rows (Access 2003 SP2)

Where did you insert the rows?

13. ## 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

14. ## 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 4-9.
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.

15. ## 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?