Page 1 of 2 12 LastLast
Results 1 to 15 of 25
  1. #1
    Platinum Lounger
    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?

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

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

  4. #4
    4 Star Lounger SteveH's Avatar
    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

  5. #5
    Plutonium Lounger
    Join Date
    Nov 2001
    Posts
    10,550
    Thanks
    0
    Thanked 7 Times in 7 Posts

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

  7. #7
    4 Star Lounger SteveH's Avatar
    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

  8. #8
    Plutonium Lounger
    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>

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

  10. #10
    3 Star Lounger
    Join Date
    Nov 2002
    Location
    New York, New York, USA
    Posts
    266
    Thanks
    0
    Thanked 19 Times in 19 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

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

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Summing rows then inserting rows (Access 2003 SP2)

    Where did you insert the rows?

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

  14. #14
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 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 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. #15
    Platinum Lounger
    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.

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •