CODEWITHSUNDEEP
arraysgoogle-sheetsgoogle-sheets-formulaarray-formulasgoogle-sheets-query
Harry
Harry

Reputation: 41

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?

Upvotes: 2

Views: 139

Answers (1)

player0
player0

Reputation: 1

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")

Upvotes: 1

Related Questions