1

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?

player0
  • 124,011
  • 12
  • 67
  • 124
J. Kubassek
  • 205
  • 2
  • 14

2 Answers2

1

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)))
LASheets
  • 141
  • 6
1

use:

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

enter image description here

and:

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

enter image description here

player0
  • 124,011
  • 12
  • 67
  • 124
  • Thank you, this is what I was looking for! May I ask if you would also happen to know how to best combine this with a filter so that blank cells would be included as well, as per the Data2 tab I've added to the sheet? I'm trying to use this information to display data as per the cells I've coloured green. Thanks again! – J. Kubassek Sep 07 '22 at 14:01
  • 1
    @J.Kubassek try: https://i.stack.imgur.com/GNuJT.png – player0 Sep 07 '22 at 14:17
  • This is fantastic, thanks a million! I have one more question regarding this, if I may - see the [Data3](https://docs.google.com/spreadsheets/d/1WiDLixHPlTLNqUvF0Wz_NNCRGItVVIpWFcdxHa0VqUI/edit#gid=1382865913) tab, how can I set up the formula when I'm sorting by two conditions while attempting to filter multiple ranges? I'm not sure why the formula I've come up with isn't displaying Range B3:U3. – J. Kubassek Sep 12 '22 at 13:22
  • 1
    @J.Kubassek see the pink cell in your Data3 sheet – player0 Sep 12 '22 at 13:35
  • I ran into an issue with this formula since the numbers are being formatted into text, and posted about it [here](https://stackoverflow.com/questions/73737760/how-to-convert-range-text-to-number-format-when-using-query-and-filter), any chance you'd be able to see if there's a way to do what I'm attempting? – J. Kubassek Sep 17 '22 at 19:28