I’m working with a spreadsheet that tracks daily operations by different team members. Each column represents a day and has the worker’s name in merged cells at the top.
I want to create monthly reports that show performance metrics for each person. To do this, I built a formula that finds the correct column ranges for each employee:
Merged cells are the worst lol. Since you’re stuck with that layout though, try using SPLIT on your formula result, then wrap each piece with INDIRECT inside MAP. Something like =MAP(SPLIT(your_formula,";"),LAMBDA(x,QUERY(INDIRECT(x),"select *"))) might work. Haven’t tested it but worth trying.
I’ve faced this issue with Google Sheets as well. Unfortunately, the platform does not provide a built-in method to convert text strings back into recognizable range references for functions like QUERY. What worked for me was to eliminate the need for string conversion entirely. I introduced a helper column that associates each data entry with the respective employee name from the header. Using QUERY with specific WHERE clauses allowed me to filter data efficiently by employee. Additionally, consider addressing the challenge of multiple ranges by executing separate QUERY functions for individual ranges and then merging them through array operations. By applying INDIRECT to each FILTER outcome separately, you maintain the integrity of the ranges instead of relying on a single, long string.
Google Sheets is treating your string as literal text, not as executable references. I’ve hit this same wall building dynamic reports. Don’t try converting the string back to ranges - restructure your approach instead. Use QUERY directly on your source data. Build a helper function that creates individual QUERY statements for each employee range, then combine the results with VSTACK or basic concatenation. IMPORTRANGE with dynamic range strings worked better for me - it handles text-to-range conversion way more reliably than INDIRECT. Or just flatten your data structure completely. Create a normalized table where each row has the date, employee name, and metrics. Makes QUERY operations dead simple without all the complex range stuff.