Feb 24 2019 01:20 PM
I am using Excel 2000 for Windows.
I have a 60-month amortization table having the following columns:
Payment# Monthly Payment Due Date Payment Date Principal Interest Principal Balance
This table begins on Row 5 Column 1.
This table has all cells correctly filled with the data for all the above column labels EXCEPT the Payment Date. The Payment Date cells will be dated as I make a monthly payment. The remaining payment dates remain blank.
My A2 cell is Titled "Current Principal Balance." The B2 cell will contain the data of the Principal Balance that is shown in the Row of the last Payment Date entered. After the payment date has been entered the Principal Balance is shown in the last column of the table for that row.
The keys are to determine the row cell with the last payment date entered.
Secondly, once that row has been determined, the principal balance for that row should populate the B2 cell (the Current Principal Balance).
I know that this code will be pretty simple for advanced Excel users. Unfortunately, my skill level does not permit me to understand combining functions properly. Once I see the code, it will help me understand how the functions work together. Please HELP!
nytwodees
Feb 26 2019 09:18 PM
Feb 28 2019 12:03 PM
Thank you Twifoo!
Your function worked perfectly.
I figured another solution. However, my method is not as elegant or economical as yours. Here it is:
To automatically fill-in cell I8
=SUMPRODUCT(MAX((D11:D75<>"")*ROW(D11:D75)))
To automatically fill-in cell H4
=INDIRECT(ADDRESS(I8, I9))
nytwodees
Feb 28 2019 05:59 PM