I need some assistance to identify where I might be making a mistake. I have a Google Sheets tab labeled ‘Sheet_Names’ that displays the following details:
| Sheet Names |
Range |
Sheet and Range |
All |
| George_Washington |
A2:C |
George_Washington!A2:C |
George_Washington!A2:C;Thomas_Jefferson!A2:C;John_Adams!A2:C;James_Madison!A2:C;James_Monroe! |
| Thomas_Jefferson |
A2:C |
Thomas_Jefferson!A2:C |
|
| John_Adams |
A2:C |
John_Adams!A2:C |
|
| James_Madison |
A2:C |
James_Madison!A2:C |
|
| James_Monroe |
|
James_Monroe! |
|
In another tab titled ‘Priorities’, I’m using the following formula in cell B2:
=QUERY({INDIRECT("'" & Sheet_Names!D2 & "'!B6:Y")},"Select * where Col1 is not null")
I anticipated that it would pull data from the specified range in each listed sheet, but instead, I am receiving an error message indicating that the range is not a valid reference. Interestingly, if I manually input the ranges as shown in my screenshot, the data populates correctly. I’m puzzled about what I might be doing wrong here. Any insights would be greatly appreciated.
Try checking if all your sheet names and references are exactly matching, even case sensitivity. If there’s small typo or misspelling in ‘Sheet_Names’, INDIRECT won’t work as expected. Also, make sure every sheet actually exists and ranges having values, cuz blank ranges might also give you troubles.
From my experience, when using the INDIRECT function in Google Sheets, it can be tricky if the references are collated in a complex manner like you have them in your ‘All’ column. Unfortunately, INDIRECT doesn’t handle semicolon-separated references across multiple sheets well. One workaround is to verify that each sheet name and range combination is correctly structured in separate indirect calls, possibly using an ARRAYFORMULA to cater to multiple entries. Also, scrutinizing any possible invisible characters, like extra spaces, could resolve the invalid reference error. Another thing to try is using a helper cell to construct these INDIRECT references individually, ensuring each reference works standalone before deploying them collectively in your query setup.
When working with INDIRECT in the manner described, ensure that the ranges specified indeed exist in all sheets. A common oversight is that different sheets might not encompass the same range dimensions. Also, if the QUERY is assessing a collective data pull, it might be beneficial to first load the data into a summary sheet using individual INDIRECT formulas to confirm the absence of data issues or reference errors. This can simplify the debugging process by allowing validation of data sources individually before utilizing a complex QUERY function.