I am trying to write a formula that will run a query on a sheet whose name is the value of a different cell.
I have this, and have tried many iterations of brackets and single/double speech marks.
=QUERY((Overview!$A2)!$A$11:$E$1024,"select A where (E = 'Absent')",0)
In this case, Overview!A2 is the cell containing the name of the sheet I want to run the query on.
>Solution :
To run a QUERY function on a sheet whose name is specified in another cell, you can use the INDIRECT function to dynamically reference the sheet name from a cell.
Assuming that the sheet name is in cell Overview!A2, you can modify your formula like this:<
=QUERY(INDIRECT("'" & Overview!$A2 & "'!$A$11:$E$1024"), "select A where E = 'Absent'", 0)
Note : INDIRECT(...) converts the constructed text string into an actual reference to the specified range on the sheet whose name is in cell Overview!$A2
Hope this helps.
Reference : Google sheets – https://support.google.com/docs/answer/3093377?hl=en