CODEWITHSUNDEEP
arraysgoogle-sheetsexcel-formulagoogle-query-languagetextjoin
Branchverse
Branchverse

Reputation: 1397

Use cell value as range for function with multiple ranges

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"

Upvotes: 1

Views: 179

Answers (1)

player0
player0

Reputation: 1

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"", )")}

enter image description here

Upvotes: 1

Related Questions