Quick, clean, and to the point

Dynamic worksheet reference

Excel formula: Dynamic worksheet reference
Generic formula 

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


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.


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:


After concatenation, we have:


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:


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:


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


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

Excel Formula Training

Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.

Download 100+ Important Excel Functions

Get over 100 Excel Functions you should know in one handy PDF.