Let's say I have two Google sheets, Doc1 and Doc2, both having only one sheet featuring two lists with the same schema, say two columns Name and Age. The lists have an unknown number of entries, but if it makes the task easier, we can assume that they have a maximum of 5 entries:

Two Google sheets

I want to collate both lists and show them in a third "Summary" document, one after the other. I can show them using these formulae, in Summary's cells A1 and A6, respectively:


However, this leaves an annoying variable number of blank lines between the two:

Unwanted blank lines

whereas I would like to show them one directly above the other, like so (values manually copied here to show what I want):

What I would like to achieve

If I try and use IMPORTRANGE on two contiguous rows (i.e. A1 and A2), I get a #REF! error ("Array result was not expanded because it would overwrite data in A2."):

#REF! error

Is it possible to achieve what I want? (Bonus points for removing the constraint about max number of items.)

  • I think I can solve this if this worked as expected.
    – Julian
    Mar 13, 2019 at 14:39

1 Answer 1

  • all you need to do is QUERY out empty rows:

=QUERY({IMPORTRANGE("1cLqgopAWG...", "Sheet1!A1:B5");
        IMPORTRANGE("1564EXoW-s...", "Sheet1!A1:B5")}, 
 "where Col1 is not null")

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.