Exceljet

Quick, clean, and to the point

Dynamic workbook reference

Excel formula: Dynamic workbook reference
Generic formula 
=INDIRECT("'["&workbook&"]"&sheet&"'!"&ref)
Summary 

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.

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.

Author 
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.