NotoriousREV

Q: Numbers: Automatically extend ranges to new rows?

Hi,

 

I have a spreadsheet that I update daily with a new row. I have a bunch of rows at the bottom of the sheet that do various things (e.g. Sum, Countif etc.) on the columns above. Is there a way of getting the range being used to automatically update to include the new row each day?

 

For example, if I have =SUM($A$1:A2) in cell A4 and I insert a new row in A3 (copying row A2 and pasting formulas), I'd like the sum to update to =SUM($A$1:A3)

 

Any ideas thankfully recieved!

Posted on Sep 8, 2015 1:42 AM

Close

Q: Numbers: Automatically extend ranges to new rows?

  • All replies
  • Helpful answers

  • by Kilgore-Trout,Helpful

    Kilgore-Trout Kilgore-Trout Sep 16, 2015 4:03 AM in response to NotoriousREV
    Level 7 (32,669 points)
    iPad
    Sep 16, 2015 4:03 AM in response to NotoriousREV

    The formula will only pick up additional rows (or columns) if the new row is inserted within the existing range. The formula has no way of determining the addition of data outside of the defined range unless you modify it accordingly. Try including a row where the cell in question is left blank and add the new row in above it.

  • by t quinn,Solvedanswer

    t quinn t quinn Sep 16, 2015 4:03 AM in response to NotoriousREV
    Level 5 (5,058 points)
    Mac OS X
    Sep 16, 2015 4:03 AM in response to NotoriousREV

    Hi NotoriousREV,

     

    Have you tried converting your summary rows to footers? I think this will get you what you want as long as your formulas include the last (non-footer) row.

     

    Before I simplified a sheet I had multiple ranges within a single column reporting to different places. I needed to add rows as Kilgore-Trout suggests. I kept an unused row below each range and added rows above them.

     

    quinn

  • by NotoriousREV,

    NotoriousREV NotoriousREV Sep 16, 2015 4:03 AM in response to NotoriousREV
    Level 1 (0 points)
    Sep 16, 2015 4:03 AM in response to NotoriousREV

    Thanks both, the blank row method worked fine and has saved me a lot of messing about daily!

  • by Kilgore-Trout,

    Kilgore-Trout Kilgore-Trout Sep 16, 2015 4:57 AM in response to NotoriousREV
    Level 7 (32,669 points)
    iPad
    Sep 16, 2015 4:57 AM in response to NotoriousREV

    You're welcome. Glad you got it sorted out.