2

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?

player0
  • 124,011
  • 12
  • 67
  • 124
Harry
  • 41
  • 3

1 Answers1

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")
player0
  • 124,011
  • 12
  • 67
  • 124
  • I want to be able to change the import range of Sheet 1 and Sheet 2 based on how many rows they have. Ie. Countif('Sheet 1'!A1:A, "<>"),Countif('Sheet 2'!A1:A, "<>") – Harry Oct 31 '19 at 14:29
  • So it may be =query({'Sheet 1'!A1:D50;'Sheet 2'!A1:D500}) or =query({'Sheet 1'!A1:D400;'Sheet 2'!A1:D15}) depending on how many rows are in each sheet – Harry Oct 31 '19 at 14:30