[Solved] Retrieve value from last used cell in a column

[ljdriver]

I have written a loan amortization schedule on OpenOffice Calc. It keeps track of principal, interest and current balance. When each payment is made I record it on the next row so the balance owed keeps moving down the calc sheet. I need to have the current balance show up in a separate cell. How can I pick out the correct number when the cell with that number moves down one row with each payment?

[MrProgrammer]

Hi, and welcome to the forum.

When each payment is made I record it on the next row so the balance owed keeps moving down the calc sheet. I need to have the current balance show up in a separate cell. How can I pick out the correct number when the cell with that number moves down one row with each payment?

If the balance always decreases, the latest value is the minimum of the values in that column so you can use the MIN function, say MIN(B2:B999) since MIN ignores empty cells. If you need any additional assistance attach a document demonstrating the situation (remove confidential information then use Post Reply, not Quick Reply, and don't attach a picture instead of the document itself).

If this solved your problem please go to your first post use the Edit button and add [Solved] to the start of the subject field. Select the green checkmark icon at the same time.

[Tutorial] Ten concepts that every Calc user should know

[ljdriver]

Thanks for the quick reply! I tried the MIN function and it works just as you described. However, I also have a column where additional cash can be added to the loan and then the balance changes (increases). With that in mind, the MIN function would not work if additional funds were added to the loan. Is there some other way that you know of where I can just take the last number in the balance column and use that number in another cell?

[ljdriver]

Amoritize.ods

(15.44 KiB) Downloaded 127 times

The MIN function works but I can't use that because additional money may be added to the mortgage and that changes the minimum number. What I need is way to keep track of the last number in the balance column as the list gets longer when payments are made.

[RusselB]

I'm unable to download your spreadsheet, but if you have your Balance column with a formula that returns "" if there's no entry, then a helper column with the formula

Code: Select all

=if(Balalnce_column<>"";row();"")

then in the cell that you want the balance to show in use

Code: Select all

=indirect(Balance_column_letter&max(helper_column))

Substitute the correct information where required.

[ljdriver]

I tried the download and it worked. I don't follow your solution and am unable to test it. I'm not even sure there is a way to do what I need. The problem is the balance moves down one row with each payment. I need to pick out the last number in the balance column and post it in a different cell so it can be used by another program

[MrProgrammer]

Amoritize.ods

Thank you for the attachment. Now I can see how you have arranged your spreadsheet. It is difficult to provide assistance without that information.

I also have a column where additional cash can be added to the loan and then the balance changes (increases). With that in mind, the MIN function would not work if additional funds were added to the loan. Is there some other way that you know of where I can just take the last number in the balance column and use that number in another cell?

Use COUNT for column D to determine how many payments have been made. Then use INDEX or OFFSET (your choice) to select the correct balance from column G. Either mark this as [Solved] or attach a document demonstrating a further difficulty.
=OFFSET($G$10;COUNT($D$11:$D$37);0)
=INDEX($G$11:$G$37;COUNT($D$11:$D$37))

Amoritization

Amortization is misspelled in cell B2.

[ljdriver]

Amoritize.ods

Thank you for the attachment. Now I can see how you have arranged your spreadsheet. It is difficult to provide assistance without that information.

I also have a column where additional cash can be added to the loan and then the balance changes (increases). With that in mind, the MIN function would not work if additional funds were added to the loan. Is there some other way that you know of where I can just take the last number in the balance column and use that number in another cell?

Use COUNT for column D to determine how many payments have been made. Then use INDEX or OFFSET (your choice) to select the correct balance from column G. Either mark this as [Solved] or attach a document demonstrating a further difficulty.
=OFFSET($G$10;COUNT($D$11:$D$37);0)
=INDEX($G$11:$G$37;COUNT($D$11:$D$37))

Amoritization

Amortization is misspelled in cell B2.

Thanks for the solution, I'll give it a try but it sure looks like it will work!
Sorry about the spelling, I noticed that after I had posted the spreadsheet. I misspelled the name of attachment also.....lol