Use cell value as range for function with multiple ranges

Question

The Goal:

using the value from a cell as range for the query function.

Problem:

it propably does not work due to it being multiple values

What I got:

// Cell value that is dynamically generated (info!A1)
"abc!$A2:$H;def!$A2:$H"

// Function I want to call
=QUERY({abc!$A2:$H;def!$A2:$H} , "where Col1 is not null")

// What I tried
=QUERY(indirect(info!A1), "where Col1 is not null")

//The error
"Function INDIRECT parameter 1 value is abc!$A2:$H;def!$A2:$H . It is not a valid cell/range reference"

score 1 · Accepted Answer · answered Apr 16 '22 at 20:06

this desired functionality is not possible. the generated range needs to be passed into INDIRECT and INDIRECT does not support arrays. ofc there are workarounds dependent on how big compromise you wish to invoke.

see: https://stackoverflow.com/a/58314247/5632629

in your scenario it would be:

={""; INDEX("=QUERY({"&TEXTJOIN("; ", 1, "INDIRECT("""&
 FILTER(A5:A, A5:A<>"")&"!"&B5&""")")&"}, ""where Col1 is not null"", )")}

Use cell value as range for function with multiple ranges

1 Answers1