CODEWITHSUNDEEP
google-sheetsgoogle-sheets-formula
impossiblehues
impossiblehues

Reputation: 11

Google Sheets Import Range if specific columns not blank

I have a Google Sheet tab ("Data") with columns A:Z and need to create a tab that pulls in all rows from ("Data") if columns A:N are NOT null in any of these columns.

I've tried query importrange, query+importrange, but can't crack the code:

Attempt #1: =IF(isblank("Data!A1:N1"),Importrange("https:... ","Data!A1:Z100"),,))

Attempt #2: =QUERY(IMPORTRANGE("https:...", " Data!A1:Z1"), "select Col1 where Col1 is not Null", 0). <<this pulls the data in for that specific cell but I can't drag it down or across to get the other rows to populate

Help!

Upvotes: 1

Views: 1362

Answers (1)

player0
player0

Reputation: 1

join columns A:N into one column and use TRIM

=INDEX(TRIM(FLATTEN(QUERY(TRANSPOSE(IMPORTRANGE("url", "Data!A1:N")),,9^9))))

next, add this column to the range A:Z as the last column of array {} and QUERY it

=INDEX(ARRAY_CONSTRAIN(QUERY({
 IMPORTRANGE("url", "Data!A1:Z"), TRIM(FLATTEN(QUERY(TRANSPOSE(
 IMPORTRANGE("url", "Data!A1:N")),,9^9)))}, 
 "where Col27 is not null"), 9^9, 26))

Upvotes: 1

Related Questions