Basically the title says it pretty well. I have a query I want to run that will continue to stack results of an ongoing event, each new event having the creation of a new sheet within this on Google Sheet.
The helper columns essentially act to concatenate the separate parts of the sheet names with the appropriate referenced cells.
My problem comes in when I try to use query and reference the populated cell as the range. I don't want Query to Query the CELL I am referencing, I want it to Query the TEXT that that cell contains. Does that make sense?
When I copy and paste the VALUE from O4, and then copy that value and paste it into the QUERY formula, it works as intended.
There is a script function "getSheetnames()" in cell E2, not sure if you need to know that to inspect the sheet.
I have gone around and around and tried every version of googling that I can think of, but all I keep finding is how to dynamically change the "query" part of the query, not the "data" part.
Here's the sheet (the tab in question is HELPER):
https://docs.google.com/spreadsheets/d/1n-fwDkeS5gtkXhPZZNszZs2_AlBeMN_2-5zo3sbUGho/edit?usp=sharing
