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.
Explanation
The CELL function can return various information about a worksheet. CELL can get things like address and filename, as well as information about the formatting used in the cell. The type of information to be returned is specified by the info_type argument .
In this example, we want the path, name, and sheet for the current workbook. To get this information, the info_type argument is set to “reference” in a formula like this:
=CELL("filename",A1)
With this configuration, CELL will return the name in this format:
C:\examples\[workbook.xlsx]Sheet1
Note that you must save the worksheet in order to get the a result.
The second argument, reference , is optional and can be any cell in the worksheet. If reference is not supplied, CELL will return the name of the current “active sheet” which may or may not be the sheet where the formula exists, and might even be in a different workbook. To avoid confusion, use A1 for reference .
