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.
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:
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 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.
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...
In this example, the goal is to use VLOOKUP to find and retrieve price information for a given product stored in an external Excel workbook. The workbook exists in the same directory and the data in the file looks like this: Note the data itself is...
The Excel INDIRECT function returns a valid cell reference from a given text string. INDIRECT is useful when you want to assemble a text value that can be used as a valid reference.
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.