0

Use case

Sort formula against other sheet but exclude empty values after last item. Empty values get sorted at top, creating a whole bunch of blank space, and then data I care about.

=SORT('other sheet'!A1:C36,'other sheet'!D1:D36,FALSE)
  • A-C is the data I wish to show.
  • D is the column I wish to sort on.

Problem

The "36" must be manually updated each time I add/remove a row to 'other sheet'.

Possible solution would be:

Get the row number of the last non-empty cell in a column in Google Sheets as [last row].

=SORT('other sheet'!A1:C[last row],'other sheet'!D1:D[last row],FALSE)

What I tried

Lookup("",'other sheet'!A:A)

  • Result: #N/A
  • No examples in Help for finding empty cells

Get the last non-empty cell in a column in Google Sheets

  • Returns value not address. Could find that value in row but not as efficient. Also what if value is found in more than one place?

** Example Speadsheet ** https://docs.google.com/spreadsheets/d/1bqiVe3pBYDJFtrO4EysSKTDq17lzY5r2b8sPV-KnTdI/edit#gid=0

paragbaxi
  • 3,965
  • 8
  • 44
  • 58

2 Answers2

0

I cannot recreate this in a new spreadsheet. I believe this may be a bug.

paragbaxi
  • 3,965
  • 8
  • 44
  • 58
0

If you want to find the last row, you can use the following formula.

=SORT(INDIRECT("'other sheet'!A1:C"&QUERY(TRANSPOSE(FILTER(ROW('other sheet'!A:A),'other sheet'!A:A="")),"select Col1")),INDIRECT("'other sheet'!D1:D"&QUERY(TRANSPOSE(FILTER(ROW('other sheet'!A:A),'other sheet'!A:A="")),"select Col1")),FALSE)

The code in bold is a formula to find the first blank cell in column A in 'other sheet'. The code in italic return a reference range based on the bolded code. I hope this help even though it seems to be a very long time since your question.

Edited: I just found out that query can limit rows.

=SORT(INDIRECT("'other sheet'!A1:C"&QUERY(FILTER(ROW('other sheet'!A:A),'other sheet'!A:A=""),"limit 1")),INDIRECT("'other sheet'!D1:D"&QUERY(FILTER(ROW('other sheet'!A:A),'other sheet'!A:A=""),"limit 1")),FALSE)

Edited: Sorry, I didn't read the question carefully. If you want to remove the first blank cell when sort in descending order, you just have to simply add a QUERY function at front, without query for anything.

=QUERY(SORT(INDIRECT("'other sheet'!A1:C"&QUERY(FILTER(ROW('other sheet'!A:A),'other sheet'!A:A=""),"limit 1")),INDIRECT("'other sheet'!D1:D"&QUERY(FILTER(ROW('other sheet'!A:A),'other sheet'!A:A=""),"limit 1")),FALSE),"")

Flyson
  • 1
  • 1
  • 2