Google Sheets – Insert Query into Specific Cell Based on Dynamic Reference

google sheetsgoogle-sheets-arraysgoogle-sheets-query

I have 4 queries. The amount of results from each will vary week-by-week.

I have a formula to calculate the number of rows of each query.

I want the queries set out in the Google Sheet so that the user can scroll down through the 4 sets of results, ideally 'one after the other', without 'artificially made gaps' in-between. (Although I cannot be certain how many results each query will provide, there is a rough upper limit which I have used a guideline to create those 'gaps'

Is there a way of somehow dynamically assigning my queries into particular cells, based on the number of results in each?

E.G. If I use my formula to calculate results from a query, I know that my first query will have 10 results, therefore I want the second query to be inserted into A11. BUT, next week, there might be 15 results in that first query, so I would want the query inserted into A16.

Is this possible?

Best Answer

It is always difficult to assess a problem or offer accurate solutions unless you share a link to your sheet (or a copy of your sheet). However, based on what you've said, I can tell you that you can combine QUERY formulas vertically in this way:

={
QUERY(A:B,"Select * Where B = 'Adam'",0);
QUERY(A:B,"Select * Where B = 'Joe'",0);
QUERY(A:B,"Select * Where B = 'Sally'",0)
}

I only separated the formula into lines for clarity; there's not need to do this in real-world application.

NOTES:

  1. The outer curly brackets signify an array.
  2. The semicolon means, in English, "Put the next element of the array directly below."
  3. The comma-zero at the end of each QUERY tells the QUERY that there are no headers; so you can leave that off or make it a comma-one for the first query if you want to include its headers.

You can also combine with OR:

=QUERY(A:B,"Select * Where B = 'Adam' OR B = 'Joe' OR B = 'Sally'",0)
Related Topic