Jon Prier
Level 1 (0 points)

Q: How do I populate the last numeric value in a column, into another cell, in another table?

I am creating an account register in Numbers, on my IPad and have been able to create a simple register. I would like to take the balance, found in column "H", and use that information elsewhere. There are currently empty cells in column "H" and may be from time to time. And obviously this is and ever growing table so the formula would need to adapt. I have searched this site and others, trying multiple formulas. Can anyone help? Thanks in advance!

iPad 2, iOS 9.2

Posted on Jan 21, 2016 12:59 PM

by t quinn,Apple recommended
t quinn t quinn
Level 5 (5,012 points)
Mac OS X
A:

Hi Jon,

 

It is possible to find the last entry in a column where that entry can be larger or smaller than the one before it may not be the simplist solution to your situation. Using "" in your empty cells can be problematic. I will use a "."

Screen Shot 2016-01-22 at 6.47.38 PM.png

INDEX-MATCH is a very useful combination. I use it nearly every place the LOOKUP functions might be used. MATCH() finds the earliest row with a "." and INDEX returns cell value in the row before that. If you skip rows in your account register this solution will fail.

 

If I was in your situation I would consider adding a footer row to my table. In that footer I would total the debits and the credits and subtract/add those to my starting balance for my balance. Any link to that footer cell will survive additional rows in the table.

 

A great resource when you are learning about Numbers are the built in templates. The personal budget template is a great demonstration of how to extract data to a summary table.

 

Welcome to Numbers!

 

quinn

Posted on Jan 22, 2016 6:07 PM

Close
Jon Prier

Q: How do I populate the last numeric value in a column, into another cell, in another table?

  • All replies
  • Helpful answers

  • by t quinn,

    t quinn t quinn Jan 22, 2016 7:55 AM in response to Jon Prier
    Level 5 (5,012 points)
    Mac OS X
    Jan 22, 2016 7:55 AM in response to Jon Prier

    Hi Jon,

     

    A little more info as to your set up might  help. There are various ways to do what you are asking. Would you post a screenshot, obscuring personal info but leaving a sense of how your table is constructed and the formula in H?

     

    quinn

  • by Jon Prier,

    Jon Prier Jon Prier Jan 22, 2016 5:32 PM in response to t quinn
    Level 1 (0 points)
    Jan 22, 2016 5:32 PM in response to t quinn

    image.png

    First of all, forgive my lack of proper terminology in reference to Numbers and spread sheets in general. My exposure to them has been limited.

     

    Here is the template that I have created, including the formula for calculating the balance. As you can see, the balance will forever move into new rows so a simple cell reference will not work. I need to find a formula that will continually adjust with the ever moving balance and can be used in other tables and on other sheets. Of course, if I can get it onto another table then a simple cell reference will likely work for me. As you can see, the last value is monetary and always will be and cells not yet holding a balance show "". Thanks for your assistance!

  • by t quinn,Apple recommended

    t quinn t quinn Jan 22, 2016 6:07 PM in response to Jon Prier
    Level 5 (5,012 points)
    Mac OS X
    Jan 22, 2016 6:07 PM in response to Jon Prier

    Hi Jon,

     

    It is possible to find the last entry in a column where that entry can be larger or smaller than the one before it may not be the simplist solution to your situation. Using "" in your empty cells can be problematic. I will use a "."

    Screen Shot 2016-01-22 at 6.47.38 PM.png

    INDEX-MATCH is a very useful combination. I use it nearly every place the LOOKUP functions might be used. MATCH() finds the earliest row with a "." and INDEX returns cell value in the row before that. If you skip rows in your account register this solution will fail.

     

    If I was in your situation I would consider adding a footer row to my table. In that footer I would total the debits and the credits and subtract/add those to my starting balance for my balance. Any link to that footer cell will survive additional rows in the table.

     

    A great resource when you are learning about Numbers are the built in templates. The personal budget template is a great demonstration of how to extract data to a summary table.

     

    Welcome to Numbers!

     

    quinn

  • by Jon Prier,

    Jon Prier Jon Prier Jan 22, 2016 6:51 PM in response to t quinn
    Level 1 (0 points)
    Jan 22, 2016 6:51 PM in response to t quinn

    Quinn,

     

    That's perfect! I used your staring balance plus credit column total minus debit column total idea. Not sure why I couldn't come up with that! It never crossed my mind. It works pefectly and is easy to transfer to other tables and sheets. Also, thanks for the Apple template idea! That will be helpfull in the next portion of my project.

     

    Thanks again!

     

    JP

  • by t quinn,

    t quinn t quinn Jan 22, 2016 7:01 PM in response to Jon Prier
    Level 5 (5,012 points)
    Mac OS X
    Jan 22, 2016 7:01 PM in response to Jon Prier

    Hi Jon,

     

    Footer rows can be so useful in situations like that.  Anything else on this topic?

     

    quinn

  • by Jon Prier,

    Jon Prier Jon Prier Jan 22, 2016 7:50 PM in response to t quinn
    Level 1 (0 points)
    Jan 22, 2016 7:50 PM in response to t quinn

    Quinn,

     

    Not currently! In the next few days I'm going to add a component for monthy budgeting and am going to utilize data from the various account registers to populate some of that data. Things like data within a particular time frame, money spent in certain catagories or at particular stores. I'm still working on the final format and will eventually need info to complete the build out. Any tips or advice is always welcome! I will know more soon about exactly what I'm looking for.

     

    Thanks,

     

    JP

  • by Jon Prier,

    Jon Prier Jon Prier Jan 25, 2016 8:38 PM in response to Jon Prier
    Level 1 (0 points)
    Jan 25, 2016 8:38 PM in response to Jon Prier

    IVe now added that budgeting portion and need to find out how to collect data from each of my registers within a particular date range. Have you seen any good threads for that?