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:


Note: the external workbook must be open for this reference to work.

Generic formula



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:


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.

Dave Bruns Profile Picture

AuthorMicrosoft Most Valuable Professional Award

Dave Bruns

Hi - I'm Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.