Search code examples
arraysgoogle-sheetsgoogle-sheets-formulaarray-formulasgoogle-sheets-query

Import Range Dynamically Based On Size

I'm trying to combine two Google Sheets together using query. For example:

=query({'Sheet 1'!A1:D50;'Sheet 2'!A1:D500})

However, the cell range that I would like to combine changes.

I've created a function that counts the rows in A.

=countif(A1:A,"<>")

Let's say it equals 26.

I then only want to import the range A1:D26.

Is there any way to easily do this?

I've tried 

=query({'Sheet 1'!A1:("D"&countif('Sheet 1'!A1));'Sheet 2'!A1:D500})

Is this something that OFFSET could be used for?

Solution

  • try to leave endrow empty:

    =QUERY({'Sheet 1'!A1:D; 'Sheet 2'!A1:D})

    and to remove empty rows:

    =QUERY({'Sheet 1'!A1:D; 'Sheet 2'!A1:D}, "where Col1 is not null")