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.

Handling spaces 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.

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.