Dynamic Sheet tab names in formulas means refer to sheet names dynamically in formulas.

If you go further deepen, it’s like switching tab names in formulas automatically or semi-automatically.

By saying semi-automatically, I mean with the help of a drop-down menu or a helper cell.

No doubt, to get dynamic sheet tab names in formulas we must use the Indirect function. But how you use the Indirect function, that is important.

The normal procedure is entering all the sheet names in a drop-down and refer to this using Indirect.

This is called the helper cell approach. But there is one more approach but limited to sheet tab names such as dates, months, weekdays etc.

Yes! You can dynamically refer to sheet tab names in formulas without referring to any helper cell.

Use today’s or tomorrow’s dates as Sheet names and get it automatically updated in formulas.

By doing so, your formula will automatically refer to the newly added sheets based on the current date.

In this Google Sheets tutorial that about dynamic sheet tab names in formulas, I am sharing these cool tips.

How to Get Dynamic Sheet Tab Names in Formulas in Google Sheets

Let me begin with the helper-cell approach. It’s simple to follow but I wish to call this a semi-dynamic formula approach.

Semi-Dynamic Sheet Names in Google Sheet Formulas Using Helper Cell

Steps:

1. Open a New Spreadsheet. Then create 3 tabs.

You can leave the default tab names as it is in this newly opened Google Spreadsheet.

In Sheet1, I am referring to Sheet2 and Sheet3 dynamically.

2. Fill some content in column 1 (A1: A) in Sheet2 and Sheet3.

3. Create a drop-down menu in Sheet1 using the Data Validation menu item.

You can apply the below settings in cell A1 in Sheet1 from the menu Data > Data Validation.

4. Apply this Indirect formula in B1.

=indirect(A1&"!A1:A")

You can include this dynamic sheet range in formulas as below.

=COUNTA(indirect(A1&"!A1:A"))

This is a semi-dynamic formula. Because we are using a helper cell A1 in this.

Note: Instead of using the drop-down you can directly type the sheet name in cell A1.

Now I am using this dynamic sheet names in SUMIF.

Dynamic Sheet Tab Names in SUMIF Formula in Google Sheets

I have this sample data in Sheet2.

How to sum the value in the range B1: B if the names in A1: A is “B”?

Formula:

=sumif(indirect(A1&"!A1:A"),"B",indirect(A1&"!B1:B"))

Enter this dynamic sheet name reference formula in any cell in Sheet1. But the cell A1 in this sheet should contain the sheet name “Sheet2”.

If you change the Shee2 in cell A1 to Sheet3, the ranges in SUMIF would change accordingly.

Similar: Dynamic Sheet Names in Importrange in Google Sheets.

Fully Dynamic Sheet Names in Google Sheet Formulas Without Using Helper Cell

In the earlier example, I have used a drop-down menu in cell A1. That means I have used a helper cell to get the dynamic sheet tab names to work in formulas.

Here is a different approach.

My Sheet tab names are dates. You can call it fully dynamic sheet names.

I have used the following formula as the range in all my formulas in the ‘Master Sheet’ tab.

=INDIRECT("'"&text(today(),"dd mmm yy")&"'!A1:B")

For example;

=counta(INDIRECT("'"&text(today(),"dd mmm yy")&"'!A1:B"))

Note: You can change the range A1: B to the range that you want.

The formulas in the ‘Master Sheet’ would automatically refer to the Sheet Tab Name which is today’s date.

So every day when you add new tabs with that day’s date as tab name, the formula in the Master Sheet will automatically refer to that sheet.

But the formula will only refresh when you add content to the newly created tab.

This is what I meant by saying fully dynamic sheet tab names in formulas in Google Sheets.

You can use the above INDIRECT formula as the range of many formulas like SUMIF, QUERY, COUNT, etc.

Can I only use dates as Sheet names to dynamically refer?

Nope! You can tweak the above formula little bit. The changes should be in the TODAY function part.

See this example.

=INDIRECT("'"&day(today())&"'!A1:B")

But I suggest you use the dates as it would be unique.

That’s all related to dynamic Sheet Tab names in formulas in Google Sheets. Hope you have enjoyed the stay!