Hi
I need to select and return text from last cell with text in column. Now i use =OFFSET(B3;COUNTIF(B3:B47;"<>")-1;0;1) but it work only if there are no empty cells between cells with text.
Thanks in advance
Yes. You surely expected tghis result.
A sometimes suggested replacement for the COUNTIF() part is
MAX((B3:B47)<>"")*ROW(B3:B47)).
The overall expression must then be evaluated in iterative array-mode.
The solution mentioned in my comment on the question has (at least) three disadvantages:
-1- The containing formula must be enterd for array-evaluation.
-2- The search range must be assured large enough. (Adaptions needed when scaling.)
-3- For large ranges it may be too inefficient.
If a user can accept to rely on user code (“macro”), it can be done rather efficient.
The attached demonstrating example contains a version of a related user function.
To avoid complications with the passing of a range as parameter, it relies on VBAsupport, and therefore it can only work in recent versions of LibreOffice having iplemented this support sufficiently.
Data is text and not numbers. Search has to be performed in column b, from b3 to b47, the result (text from last cell with text in column) has to be shown in cell c48.
Aha…