How to use a difference cell's value in 'data' field of Sheets QUERY

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