# 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.

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 …?

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

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.

can this be adapted to get a range? last 5 cells for a row?