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))))
Do you mean that some rows are hidden?
Or do you use formatting to make some values invisible?
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))))