Forum Discussion
Excel help!
How would I capture the data from the last cell in a column when the column length is variable?
9 Replies
Thanks for all the suggestions! Something I failed to tell is that when this mortgage table calculates the new value for number of payments the column will also shorten to this new value, and the remaining values will not be visible in the column. I need it to find the last visible data or value in this column.
Thanks!
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.
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)I'm sorry, I don't understand what you mean. Can you provide more detailed information?
I'm working on a mortgage calculation worksheet and the column that will show the number of payments will vary in length depending on extra payments. I'm trying to capture that new number since it moves up that column. For example, for a std 30-year mortgage it will be 360 months and in row 360. If I make extra payments to pay off in 20 years it calculates 240 months, and the new value is in line 240. I want it to find that last value in a variable length column. I hope that helps explain.
Let's say the column is column K.
If it contains numbers or dates, you can use
=LOOKUP(9.99999999999999E+307, K:K)
If it contains text values, you can use
=LOOKUP(REPT("z", 255), K:K)
If if contains a mixture of values, you can use
=INDEX(K:K, MAX(IF(K:K<>"", ROW(K:K))))
