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))))
That worked great for finding the last value in a fixed length column but how do I do that if the column length varies based on other input. For instance, the last figure is in line 250 and based on other input new last line is now 200. How do I automatically capture that varying cell.
If you are on Excel365 then could utilize trimrange feature or function. Try-
=TAKE(A.:.K,-1,-1)Or
=TAKE(TRIMRANGE(A:K),-1,-1)If there is chance to have blank cells on last column bottom rows then could try-
=TAKE(TOCOL(TAKE(A.:.K,,-1),1),-1)