# Select last cells with a value for a given row

Hello all, My very first post. happy days.

I am trying to select the last 5 cells that contain a value (number) from a row (very long row with lots of gaps).

Ideally I would like to get the last 5 values for a number of rows from sheet 1 and have those end up in sheet 2 (a lovely summary with the last 5 results).

I can update manually but if I could automate this so that values are sent to sheet 2 as I update sheet 1 that would be fantastic.

thank you.

edit retag close merge delete

What would be "the last 5 values for a number of rows"; the last 5 numbers across an arbitrary number of rows, or the last 5 values of one row for an arbitrary number of rows, or ...?

( 2019-06-25 17:25:49 +0100 )edit

Hi. Apologies if this was not clear.

I want to get the value for the last 5 cells in any given row.

By that I mean I want to extract whatever is written in the last 5 cells of a row number or characters or mixed.

The problem is I have gaps in the rows. That is some cells are blank. So I need to tell the spreadsheet to ignore empty cells in the row and only get the last 5 cells that actually contain something.

Hope this is a bit clearer.

thanks everyone

( 2019-06-25 18:21:47 +0100 )edit

Sort by » oldest newest most voted

To lookup the last value within one given row:

=LOOKUP(2;1/(Sheet1.42:42<>"");Sheet1.42:42)


which retuns the last value in row 42 on Sheet1.

If you have mixed text and numeric values within a row and want the last numeric value then

=LOOKUP(2;1/ISNUMBER(Sheet1.42:42);Sheet1.42:42)


All this needs at least LibreOffice 6.1 to work.

Update: 2019-06-25T20:38+02:00

To obtain the last 5 numeric values of one row use

=LOOKUP(LARGE(IF(ISNUMBER(Sheet1.42:42);COLUMN(Sheet1.42:42));{5,4,3,2,1});COLUMN(Sheet1.42:42);Sheet1.42:42)


entered as array formula (close with Shift+Ctrl+Enter instead of just Enter), it will then spread over 5 columns. Note that within the inline array {5,4,3,2,1} the array column separator (here , comma) needs to be used, configured under Tools -> Options -> Calc -> Formula, which depends on the locale. The formula returns #VALUE! error in all array elements if less than 5 numeric values are available.

more