How to Include Upcoming Sheets in Google Sheets Formulas

You can include upcoming sheets in Google Sheets formulas whether you already know their names or not. There are three approaches, and you can choose the one that best fits your situation.

Hardcoded sheet references in the formula (when you already know the sheet names)
Using a list of sheet names (when you already know the sheet names)
Using an Apps Script to generate a list of sheet names (when you don’t know the sheet names yet)

Let’s see all three approaches.

Table of Contents

Method 1: Hardcode Upcoming Sheet Names in the Formula

Assume you have three sheets in a Google Sheets workbook: Jan, Feb, and Mar.

The upcoming sheets are Apr, May, and Jun. How do you include these upcoming sheets in the formula beforehand?

Assume the data is in A1:B in all the sheets.

In an empty sheet, enter the following formula in cell A2:

=LET(
  range, IFERROR(VSTACK(
    Jan!A2:B, Feb!A2:B, Mar!A2:B, Apr!A2:B, May!A2:B, Jun!A2:B
  )), 
  FILTER(range, CHOOSECOLS(range, 1)<>"")
)

We use the data starting from row 2 in each sheet to skip the header row.

The formula vertically combines the data from all the existing sheets. When you later add the upcoming sheets, their data will automatically be included without changing the formula.

You can use this as a helper range in other formulas to ensure they always include data from the upcoming sheets.

How the Formula Works

When you include upcoming sheets in VSTACK, any sheet that doesn’t exist yet returns an error. IFERROR suppresses those errors, leaving empty rows instead.

FILTER then removes the empty rows. In the formula above, CHOOSECOLS(range, 1) checks the first column to identify empty rows. You can change it to another column if needed. In some cases, the first column may be intentionally blank while another column in the same row contains data.

Note: This also applies to Methods 2 and 3, where CHOOSECOLS(data, 1) is used for the same purpose.

Method 2: Reference Upcoming Sheet Names from a List

This is another method to include upcoming sheets in Google Sheets formulas. Unlike the previous method, this one is dynamic.

Enter the sheet names in an empty column in a new sheet, for example, A2:A. In this example, enter Jan, Feb, Mar, Apr, May, and Jun in A2:A7.

Then enter the following formula in cell B2.

=LET(
  tabList, TOCOL(A2:A, 3), 
  helper, MAP(tabList, LAMBDA(r, AND(r<>"", IFERROR(SHEET(TO_TEXT(r)), 0)))), 
  dataRange, "A2:B", 
  REDUCE(
    TOCOL(, 3),
    FILTER(tabList, helper), 
    LAMBDA(acc, val, 
      VSTACK(acc, LET(data, INDIRECT(val&"!"&dataRange), FILTER(data, CHOOSECOLS(data, 1)<>"")))
    )
  )
)

In this formula:

A2:A contains the list of sheet names, including the upcoming sheets.
“A2:B” specifies the range to retrieve from each sheet.

The formula first checks whether each sheet in the list exists. It then retrieves the specified range from the existing sheets only and vertically combines the data into a single array. As you add the upcoming sheets to the workbook, their data is automatically included without modifying the formula.

I prefer this method when you already know the names of the upcoming sheets, especially if your workbook contains many sheets.

Method 3: Reference Upcoming Sheet Names from a List (Apps Script)

In this method, we will use an Apps Script to generate a list of available sheet names. Whenever you add, rename, or delete a sheet, the list is automatically updated. Here’s how to set it up.

Add a new sheet to the workbook and name it Sheet List.
Click Extensions > Apps Script.
In the editor, remove the existing code and paste the following script.

function updateSheetList() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const listSheet = ss.getSheetByName("Sheet List");

  const names = ss.getSheets().map(s => [s.getName()]);

  listSheet.getRange("A:A").clearContent();
  listSheet.getRange(1, 1, names.length, 1).setValues(names);
}

Click the project name (Untitled project) and rename it, for example, tablist.
Click Save project.
Click Run and authorize the script when prompted.
Click the Triggers (clock) icon.
Click Add Trigger.
Select updateSheetList as the function to run.
Set the event type to On change, and then save the trigger.

The Sheet List sheet will now be updated automatically whenever sheets are added, renamed, or deleted.

To combine the data, use the following formula:

=LET(
  tabList, TOCOL(A2:A, 3), 
  dataRange, "A2:B", 
  REDUCE(
    TOCOL(, 3),
    tabList, 
    LAMBDA(acc, val, 
      VSTACK(acc, LET(data, INDIRECT(val&"!"&dataRange), FILTER(data, CHOOSECOLS(data, 1)<>"")))
    )
  )
)

Note: The Sheet List sheet name is stored in cell A1, so the formula starts from A2 to exclude it.

This formula is the same as the one used in Method 2, except that it doesn’t need to check whether the sheet names exist because the Apps Script generates a list of existing sheets only.

Conclusion

You have seen three methods to include upcoming sheets in Google Sheets formulas. Choose the one that best fits your workflow.

If you ask me, I prefer the second method because it is dynamic and doesn’t require an Apps Script.