Easiest way to query multiple sheets that are named by year, for all time?

Question

I use a big nasty formula to query multiple sheets in the same Google Sheet document named according to year (2020, 2019, 2018, etc...) to sum up a total value. Because I need to query a filtered range in a complex way, I've figured out the best way to do this without running into other troubleshooting issues is to SUM multiple queries like so:

=SUM(
IFERROR(QUERY(FILTER({EOMONTH(INDIRECT("'"&**TO_TEXT(YEAR(TODAY()))&"'!A1:A"&ROWS(INDIRECT("'"&TO_TEXT(YEAR(TODAY()))&"'!K2:K"))), 0),INDIRECT("'"&TO_TEXT(YEAR(TODAY()))&"'!K2:K")}, [filter conditions]), "select Col2 label Col2' ' ")), IFERROR(QUERY(FILTER({EOMONTH(INDIRECT("'"&TO_TEXT(YEAR(TODAY()-365))&"'!A1:A"&ROWS(INDIRECT("'"&TO_TEXT(YEAR(TODAY()-365))&"'!K2:K"))),0),INDIRECT("'"&TO_TEXT(YEAR(TODAY()-365))**&"'!K2:K")}, [filter conditions]), "select Col2 label Col2' ' "))
)

For some context, you can see the much larger IF formula that this SUM is meant to be nested into, in the "Example Matrix" tab of the sheet. My focus for this question is on the INDIRECT references, which I have been using to dynamically reference the most current year's sheet and the previous year's sheet. The problem is, if I want to keep doing this for every sheet as the years go on, I have to manually add a whole other query into my SUM using INDIRECT("'"&TO_TEXT(YEAR(TODAY()-730))&"'!K2:K") and INDIRECT("'"&TO_TEXT(YEAR(TODAY()-1095))&"'!K2:K") and so on, and that is just not an option considering how many of them I would need to add to multiple formulas in multiple sheets.

Is there any way I can adapt this for simplicity or perhaps make it into a script to accomplish summing queries for all sheets that are named by year for all time?

Here's a copy of my Example Sheet: https://docs.google.com/spreadsheets/d/1b29gyEgCDwor_KJ6ACP2rxdvauOzacDI9FL2K-jgg5E/edit#gid=1652431688

Thank you, any help is appreciated.

Answer 1

Usually an array formula would be a way to go in such case, but INDIRECT does not work inside array formulas.

There are a few approaches using scripting like this.

Here I will describe another approach: formula generation. We'll get a string with the formula and manually place it in a cell. It would be nice to put it in an inverted FORMULATEXT function, but unfortunately there is no such function at the moment, so we'll just paste it manually.

Step 1

Set the year limits (sheet names) in some cells. The first year of the period will be in K22, and the last will be in M22.

I set the period to from 2005 to 2040.

All the year numbers will e easily generated with SEQUENCE. If there were arbitrary names, a range of those names set manually would've been needed.

Step 2

Write a formula generator for what you need. We just generate a string here, in that string will be a formula you would normally type manually. It is not hard, but there are a lot of repetition and it would be tedious to write it manually.

Here is the generator:

=ARRAYFORMULA(
  "=SUM(
  FILTER(
    {
      " & JOIN(
            ";" & CHAR(10) & "      ",
            "IFERROR('" & SEQUENCE(M22 - K22 + 1, 1, K22, 1) & "'!D2:D, 0)"
          ) & "
    },
    ISNUMBER(
      {
        " & JOIN(
              ";" & CHAR(10) & "        ",
              "IFERROR('" & SEQUENCE(M22 - K22 + 1, 1, K22, 1) & "'!D2:D, 0)"
            ) & "
      }
    ),
    REGEXMATCH(
      {
        " & JOIN(
              ";" & CHAR(10) & "        ",
              "IFERROR('" & SEQUENCE(M22 - K22 + 1, 1, K22, 1) & "'!A2:A, 0)"
            ) & "
      },
      ""(?i)^TOTAL$""
    ),
    REGEXMATCH(
      {
        " & JOIN(
              ";" & CHAR(10) & "        ",
              "IFERROR('" & SEQUENCE(M22 - K22 + 1, 1, K22, 1) & "'!C2:C, 0)"
            ) & "
      },
      ""(?i)^"" & IF(F19 = ""Condition 1 Count"", ""Condition 1"", ""Condition 2"") & ""$""
    )
  )
)"
)

Compared to the original formula the resulting formula is heavily changed, simplified. For example there is no actual need for INDIRECT with this approach, EOMONTH wasn't used anywhere and so on.

Step 3

Copy that result as text, remove enclosing quotes, replace double double quotes with single double quotes: "" -> ".

Now we've got our formula to paste somewhere as we could've typed manually. Here is a part of it:

=SUM(
  FILTER(
    {
      IFERROR('2005'!F2:F, 0);
      IFERROR('2006'!F2:F, 0);
      ...
      IFERROR('2039'!F2:F, 0);
      IFERROR('2040'!F2:F, 0)
    },
    ISNUMBER(
      {
        IFERROR('2005'!F2:F, 0);
        IFERROR('2006'!F2:F, 0);
        ...
        IFERROR('2039'!F2:F, 0);
        IFERROR('2040'!F2:F, 0)
      }
    ),
    REGEXMATCH(
      {
        IFERROR('2005'!C2:C, 0);
        IFERROR('2006'!C2:C, 0);
        ...
        IFERROR('2039'!C2:C, 0);
        IFERROR('2040'!C2:C, 0)
      },
      "(?i)^TOTAL$"
    ),
    REGEXMATCH(
      {
        IFERROR('2005'!E2:E, 0);
        IFERROR('2006'!E2:E, 0);
        ...
        IFERROR('2039'!E2:E, 0);
        IFERROR('2040'!E2:E, 0)
      },
      "(?i)^" & IF(F19 = "Condition 1 Count", "Condition 1", "Condition 2") & "$"
    )
  )
)

Step 4

Manually place this resulting formula into some cell.

It does what it supposed to do, dropdown reference works, non-existing sheets are tolerated.

There is no 2021 sheet for example, but when it will be crated there will be no need to change the formula, data from that new sheet will be used.

You'll need to repeat the process in two cases: the formula needs some change in logic or it is almost 2040 and you want to add another 50 years to the period. Still that process of generation is faster than making changes manually to the resulting monster.

---

A few notes on the original formula:

• YEAR(TODAY() - 365) ➡ YEAR(TODAY()) - 1. With your approach there will be an error because of leap years. Depends on the years number, but at the beginning of a year it will emerge for sure.
• "select Col2 label Col2' ' " ➡ "select Col2 label Col2 ''". Do you really need a column with a header name ' ' (just a space)? I'm guessing it meant to be blank.
• No need for TO_TEXT.