Best formula to display data from last X number of columns in a row?

J. Kubassek 0
Viewed 42

I need to display the last X number of cells containing data from the columns on my Data sheet in a row on my Horizontal sheet. In this example, I'm trying to return the last 10 cells with data.

I'm able to display the last 10 cells containing data from the rows in Column A on the Data sheet by using this formula in my Vertical sheet:

=QUERY(Data!A2:A,"limit 10 offset "&(COUNT(Data!A2:A)-10))

...but the one that evades me is how to do this with the last 10 columns in a row of data in Row 1 on my Data sheet. Simply doing this gives me the 'Query completed with an empty output' error:

=QUERY(Data!B1:1,"limit 10 offset "&(COUNT(Data!B1:1)-10))

I've searched far and wide for the solution to this one, but it seems most people use data in a column instead of in a row. Any ideas?

offset google-query-language flatten google-sheets-formula arrays
2 Answers

You can transpose the data within the query in order to get a vertical list of the last 10 values, like this:

=QUERY(TRANSPOSE(Data!B1:1),"limit 10 offset "&(COUNT(Data!B1:1)-10))

If you need it horizontally, transpose the whole formula again to retain the horizontal orientation.

=TRANSPOSE(QUERY(TRANSPOSE(Data!B1:1),"limit 10 offset "&(COUNT(Data!B1:1)-10)))
LASheets0

use:

=OFFSET(A1, COUNTA(A:A)-10,,10)

enter image description here

and:

=OFFSET(A1,,COUNTA(1:1)-10,,10)

enter image description here

player00
Related