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:
=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:
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.