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

Discuss the spreadsheet application

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

Postby ljdriver » Thu Sep 10, 2020 7:46 pm

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?
Last edited by ljdriver on Tue Sep 15, 2020 6:22 pm, edited 2 times in total.
OpenOffice 4.1.6 on Windows 10 Home
ljdriver
 
Posts: 5
Joined: Thu Sep 10, 2020 7:21 pm

Re: Retrieve number from cell as it changes position in a co

Postby MrProgrammer » Thu Sep 10, 2020 8:02 pm

Hi, and welcome to the forum.

ljdriver wrote: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
Mr. Programmer
AOO 4.1.7 Build 9800 on MacOS 10.14.6.   The locale for any menus or Calc formulas in my posts is English (USA).
User avatar
MrProgrammer
Moderator
 
Posts: 3961
Joined: Fri Jun 04, 2010 7:57 pm
Location: Wisconsin, USA

Re: Retrieve number from cell as it changes position in a co

Postby ljdriver » Thu Sep 10, 2020 8:34 pm

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?
OpenOffice 4.1.6 on Windows 10 Home
ljdriver
 
Posts: 5
Joined: Thu Sep 10, 2020 7:21 pm

Re: Retrieve number from cell as it changes position in a co

Postby ljdriver » Sun Sep 13, 2020 10:06 pm

Amoritize.ods
(15.44 KiB) Downloaded 11 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.
OpenOffice 4.1.6 on Windows 10 Home
ljdriver
 
Posts: 5
Joined: Thu Sep 10, 2020 7:21 pm

Re: Retrieve number from cell as it changes position in a co

Postby RusselB » Mon Sep 14, 2020 5:14 am

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   Expand viewCollapse view
=if(Balalnce_column<>"";row();"")
then in the cell that you want the balance to show in use
Code: Select all   Expand viewCollapse view
=indirect(Balance_column_letter&max(helper_column))


Substitute the correct information where required.
OpenOffice 4.1.7 and LibreOffice 6.3.3.2 on Windows 7 Pro & Ultimate
If you believe your problem has been resolved, please go to your first post in this topic, click the Edit button and add [Solved] to the beginning of the Subject line.
User avatar
RusselB
Moderator
 
Posts: 6110
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON

Re: Retrieve number from cell as it changes position in a co

Postby ljdriver » Mon Sep 14, 2020 4:33 pm

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
OpenOffice 4.1.6 on Windows 10 Home
ljdriver
 
Posts: 5
Joined: Thu Sep 10, 2020 7:21 pm

Re: Retrieve number from cell as it changes position in a co

Postby MrProgrammer » Tue Sep 15, 2020 3:28 pm

ljdriver wrote: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.

ljdriver wrote: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))


Cell B2 wrote:Amoritization
Amortization is misspelled in cell B2.
Mr. Programmer
AOO 4.1.7 Build 9800 on MacOS 10.14.6.   The locale for any menus or Calc formulas in my posts is English (USA).
User avatar
MrProgrammer
Moderator
 
Posts: 3961
Joined: Fri Jun 04, 2010 7:57 pm
Location: Wisconsin, USA

Re: Retrieve number from cell as it changes position in a co

Postby ljdriver » Tue Sep 15, 2020 5:55 pm

MrProgrammer wrote:
ljdriver wrote: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.

ljdriver wrote: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))


Cell B2 wrote: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
OpenOffice 4.1.6 on Windows 10 Home
ljdriver
 
Posts: 5
Joined: Thu Sep 10, 2020 7:21 pm


Return to Calc

Who is online

Users browsing this forum: charlesjoseph, FJCC and 17 guests