Summary

To create a formula with a dynamic sheet name you can use the INDIRECT function. In the example shown, the formula in C6 is:

=INDIRECT(B6&"!A1")

Note: The point of INDIRECT here is to build a formula where the sheet name is a dynamic variable. For example, you could change a sheet name (perhaps with a drop-down menu) and pull in information from different worksheet.

Generic formula

=INDIRECT(sheet_name&"!A1")

Explanation 

The INDIRECT function tries to evaluate text as a worksheet reference. This makes it possible to build formulas that assemble a reference as text using concatenation, and use the resulting text as a valid reference.

In this example, we have Sheet names in column B, so we join the sheet name to the cell reference A1 using concatenation:

=INDIRECT(B6&"!A1")

After concatenation, we have:

=INDIRECT("Sheet1!A1")

INDIRECT recognizes this as a valid reference to cell A1 in Sheet1, and returns the value in A1, 100. In cell C7, the formula evaluates like this:

=INDIRECT(B7&"!A1")
=INDIRECT("Sheet2!A1")
=Sheet2!A1
=200

And so on, for each formula in column C.

Space and punctuation in sheet names

If sheet names contain spaces or punctuation characters, you'll need to adjust the formula to wrap the sheet name in single quotes (') like this:

=INDIRECT("'"&sheet_name&"'!A1")

where sheet_name is a reference that contains the sheet name. For the example on this page, the formula would be:

=INDIRECT("'"&B6&"'!A1")

Note this requirement is not specific to the INDIRECT function. Any formula that refers to a sheet name with space or punctuation must enclose the sheet name in single quotes.

Related formulas

Excel formula: Lookup with variable sheet name

Lookup with variable sheet name

In this example, the goal is to create a lookup formula with a variable sheet name. In other words, a formula that uses a sheet name typed into a cell to construct a reference to a range on that sheet. If the sheet name is changed, the reference should update automatically. The key to the solution...
Excel formula: Sum across multiple worksheets with criteria

Sum across multiple worksheets with criteria

In this example, the goal is to sum hours per project across three different worksheets: Sheet1, Sheet2, and Sheet3. The data on each of the three sheets has the same structure as Sheet1, as seen below: 3D reference won't work Before we look at a solution, let's look at something that doesn't work...
Excel formula: Dynamic workbook reference

Dynamic workbook reference

In this example, the goal is to create a reference to an external workbook with variable information. The easiest way to do this is to assemble the reference to a range or cell in another workbook as a text value , then use the INDIRECT function to convert the text to an actual reference. In Excel...
Excel formula: Get sheet name only

Get sheet name only

In this example, the goal is to return the name of the current worksheet (i.e. tab) in the current workbook with a formula. This is a simple problem in the latest version of Excel, which provides the TEXTAFTER function . In older versions of Excel, you can use an alternative formula based on the...
Excel formula: Indirect named range different sheet

Indirect named range different sheet

The formula above evaluates something like this: =SUM(INDIRECT("'"...
Excel formula: Increment cell reference with INDIRECT

Increment cell reference with INDIRECT

Consider a simple dynamic reference to Sheet2 using the INDIRECT in a formula like this: =INDIRECT($B$5&"!"&"A1") If we change the sheet name in B5 to another (valid) name, INDIRECT will return a reference to A1 in the new sheet. However, if we copy this formula down the column, the...

Related functions

Excel INDIRECT function

INDIRECT Function

The Excel INDIRECT function returns a valid cell reference from a given text string. INDIRECT is useful when you want to assemble a text value that can be used as a valid reference.
Dave Bruns Profile Picture

AuthorMicrosoft Most Valuable Professional Award

Dave Bruns

Hi - I'm Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.

Formulas

Functions