How to return multiple ranges from different sheets in one QUERY

Question

I am currently combining two queries that are looking in two sheets.

={query(Sheet1!A3:D,"select * where A is not null",-1);query(Sheet2!A3:D,"select * where A is not null",-1)}

Knowing that in the future I could have 10 different sheets to gather data from, I'd like to create a reference sheet, where I just write the name of the sheet in a range and the query picks this up dynamically. Here is how this would be pieced together potentially.

ReferenceSheet!E2&"!A3:D" --> Sheet1!A3:D

So instead of putting in the range manually and stich one query after another, I'd like to create the Data Range based on the reference sheets data and look through all of them together. So Sheet1 and Sheet2 are combined in one query. My current approach looks like this:

=query(ReferenceSheet!E3&"!A3:V" & ";" & ReferenceSheet!E4&"!A3:V" & ";" & ReferenceSheet!E5&"!A3:V","select * where Col1 is not null",-1)

This causes two issues:

This range, even tough it gives me the correct "text" is actually not working in the query. I think half of the dynamic range gets ignored and its just "seeing" the Reference Sheet part of it. Thats what I get out of the query:

I have to type in each reference manually. So its not really improving the original approach.

In case anybody wants to try this out: docs.google.com/spreadsheets/d/… Database!A2 is where the query is hidden :) — Dennis
– Dennis, Commented Apr 23, 2021 at 20:02

MattKing · Accepted Answer · 2021-04-23 15:47:33Z

1

what you're describing is an extremely common question and is not possible the way you're hoping. the best thing to do is create the tabs NOW , but leave them blank and hide them. Then you can build your query the way you're used to even though for the time being those tabs will just be blank.

answered Apr 23, 2021 at 15:47

MattKing

7,80310 silver badges13 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Dennis Over a year ago

What I currently have is at least working to some degree =query(indirect(Glossar!E3&"!A3:D"),"select * where A is not null",-1) I just need to find a way now to return more than one CELL from the reference sheet (Glossar)

Collectives™ on Stack Overflow

How to return multiple ranges from different sheets in one QUERY

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related