google-sheetsgoogle-sheets-formula

Error trying to get list of sheet names and ranges in query formula in Google Sheets

Perhaps someone could point out where I am going wrong here.

I have a tab in a Google Spreadsheet named "Sheet_Names". The data in that tab looks like this.

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!

All of the sheet names are grouped in Sheet_Names!D2. In another tab named "Priorities", I am using this formula in cell B2: =QUERY({INDIRECT("'" & Sheet_Names!D2 & "'!B6:Y")},"Select * where Col1 is not null"). I was expecting the data from A2:C from each listed tab to display. Instead, I get the following an error stating that the range is not a valid cell/range reference.

Screenshot 1

However, if I actually type these ranges into the formula exactly as shown in screenshot, then the data from each tab displays as expected.

Clearly I am doing something wrong, but I cannot come up with a solution.

Solution

  • You can't use array literals with INDIRECT. Try

    =QUERY(
  REDUCE(
    TOCOL(,1),
    TOCOL(SheetNames!C2:C,1),
    LAMBDA(a,c,VSTACK(a,IFERROR(INDIRECT(c))))),
  "where Col1 is not null")

    Also, you should put B6:Y in SheetNames!B5 so it's consistent with the other ranges.