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

Question

Level 1

0 points

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

Reply

Answer 1

t quinn

Level 5

7,063 points

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

Reply

Answer 2

Jon Prier Author

Level 1

0 points

Jan 22, 2016 5:32 PM in response to t quinn

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!

Reply

Answer 3

t quinn

Level 5

7,063 points

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

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

Reply

Answer 4

Jon Prier Author

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

Reply

Answer 5

t quinn

Level 5

7,063 points

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

Reply

Answer 6

Jon Prier Author

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

Reply

Answer 7

Jon Prier Author

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?

Reply