Google Sheets - How to use results from INDEX formula in a new QUERY function

Question

In Google Sheets I have an =INDEX function which is producing a number of references for data in another worksheet.

I need to use those references in a QUERY function.

I've not found a good way and keep getting 'formula parse' or 'array' errors. If you have any assistance you might be able to offer on how to achieve this I'd be really appreciate of some help.

The INDEX Function I'm using is:

=INDEX(FLATTEN(FLATTEN(
 FILTER(O3:O, O3:O<>"")&""&TRANSPOSE(
 FILTER(P3:P, P3:P<>"")))&""&TRANSPOSE(
 FILTER(Q3:Q, Q3:Q<>""))))

This produces results like this: All good so far...

Next, I need to somehow use those results as part of a QUERY function. I've got the query function working with the results typed in (see below):

But I can't figure out how to dynamically populate the QUERY function using the initial INDEX results. What's the best way to approach this?

Answer 1

You apparently have several tabs to import data from. If they are all in the same spreadsheet file, you can Insert > New sheet there and collect all pertinent data to that sheet with simple references like =Emma!Hours or an { array expression }. You can then import the collected data table with one simple importrange().

If the data is scattered across multiple spreadsheet files, you may want to explore the ImportMultipleRanges script. Because of certain changes in the Google Sheets security model, the script no longer works as a directly callable custom function, but you can still use it through a time-driven trigger as described in this topic.