Explanation
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, a full reference to an external worksheet looks like this:
'[sample data.xlsx]Sheet1'!A1
Note the square brackets ([ ]) around workbook name, single quotes (’ ‘) around the worksheet + sheet, and the exclamation mark (!) that follows.
To create a reference like this using text, we use concatenation to join values from columns B, C, and D with the required brackets, quotes, and exclamation mark:
=INDIRECT("'["&B6&"]"&C6&"'!"&D6)
The result is fed into INDIRECT as ref_text . Once the concatenation is performed, we have:
=INDIRECT("'[sample data.xlsx]Sheet1'!A1")
The INDIRECT function then evaluates the text and converts it to a genuine reference, and Excel follows the reference and returns the value at the given reference.
Note: if the reference is invalid, or if the workbook referenced is not open , INDIRECT will throw a #REF error. You can catch this error with the IFERROR function and display a custom result if you like.
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.
