Ask Your Question

Select last cells with a value for a given row

asked 2019-06-25 13:22:17 +0100

sr1pulpo gravatar image

updated 2020-08-11 14:28:03 +0100

Alex Kemp gravatar image

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 flag offensive 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 ...?

erAck gravatar imageerAck ( 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

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

1 Answer

Sort by » oldest newest most voted

answered 2019-06-25 17:16:05 +0100

erAck gravatar image

updated 2019-06-25 20:39:08 +0100

To lookup the last value within one given row:


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


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


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.

edit flag offensive delete link more


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

sr1pulpo gravatar imagesr1pulpo ( 2019-06-25 18:26:24 +0100 )edit
Login/Signup to Answer

Question Tools

1 follower


Asked: 2019-06-25 13:22:17 +0100

Seen: 1,293 times

Last updated: Jun 25 '19