Dynamic workbook reference

=INDIRECT("'["&workbook&"]"&sheet&"'!"&ref)
To build a dynamic worksheet reference – a reference to another workbook that is created with a formula based on information that may change – you can use a formula based on the INDIRECT function. In the example shown, the formula in E6 is:
=INDIRECT("'["&B6&"]"&C6&"'!"&D6)
Note: the external workbook must be open for this reference to work.
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.
Download 100+ Important Excel Functions
Get over 100 Excel Functions you should know in one handy PDF.