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:
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:
=IMPORTRANGE("1cLqgopAWG...","Sheet1!A1:B5")
=IMPORTRANGE("1564EXoW-s...","Sheet1!A1:B5")
However, this leaves an annoying variable number of blank lines between the two:
whereas I would like to show them one directly above the other, like so (values manually copied here to show what I want):
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."):
Is it possible to achieve what I want? (Bonus points for removing the constraint about max number of items.)