Forum Discussion
Excel help!
Thanks to SergeiBaklan​ I may now understand what you want. To get the last displayed value in column B, use
=INDEX(B1:B100, MAX(IF(($B$14:$B$100<>"")*(($D$14:$D$100>0)+($F$14:$F$100>0)), ROW($D$14:$D$100))))
Adjust the column in B1:B100 for the last displayed value in other columns, for example for column K:
=INDEX(K1:K100, MAX(IF(($B$14:$B$100<>"")*(($D$14:$D$100>0)+($F$14:$F$100>0)), ROW($D$14:$D$100))))
Yes, the rows that are no longer used because of a shorter payment schedule become invisible. I want to locate that last visible cell.
I think you need VBA for that, so it will work only in the desktop version of Excel for Windows and Mac.
Press Alt+F11 to activate the Visual Basic Editor.
Select Insert > Module.
Copy the following code into the new module:
Function LastValue(r As Range) As Variant
Dim w As Worksheet
Set w = r.Parent
LastValue = w.Cells(w.Rows.Count, r.Column).End(xlUp).Value
End FunctionSwitch back to Excel.
The last visible value in column K is returned by the formula
=LastValue(K:K)
Save the workbook as a macro-enabled workbook (*.xlsm), and make sure that you allow macros when you open it.
Thanks again for helping. I got all that in and now all I get is a 0. I've attached several screen shots of what I'm trying to do. In the beginning you will see $0.00 optional early payments and 360 actual total payments. Then I'll pay $25.00 in optional early payment, and it was reduced to 346 actual total payments and 347-360 are no longer visible.
Thanks to SergeiBaklan​ I may now understand what you want. To get the last displayed value in column B, use
=INDEX(B1:B100, MAX(IF(($B$14:$B$100<>"")*(($D$14:$D$100>0)+($F$14:$F$100>0)), ROW($D$14:$D$100))))
Adjust the column in B1:B100 for the last displayed value in other columns, for example for column K:
=INDEX(K1:K100, MAX(IF(($B$14:$B$100<>"")*(($D$14:$D$100>0)+($F$14:$F$100>0)), ROW($D$14:$D$100))))
Or we may copy/paste conditional formatting rule
=XLOOKUP( 0, (PaymentSchedule3[Payment number]="")+(PaymentSchedule3[Beginning balance]=0)*(PaymentSchedule3[Extra payment]=0), PaymentSchedule3[Payment number],,,-1 )
Have no idea why answer in text was blocked
Could you attach a small sample workbook demonstrating the problem (without sensitive data), or if that is not possible, make it available through OneDrive, Google Drive, Dropbox or similar?
