MS Excel has a vast array of built-in functions that you can use in your formulas to manipulate your data. The main Excel function that I will demonstrate in this article is the INDIRECT function. This is a nice piece of tool to easily create references to external Excel files in your formulas.
The INDIRECT worksheet function of MS Excel returns the contents of the specified reference and displays its contents. The INDIRECT worksheet function can be used to create “linked references” to other workbooks. Using the INDIRECT function, you can reference each attribute of the reference (workbook name, worksheet name, and cell reference) individually to create a user-defined dynamic reference with worksheet cell references as inputs.
In other words, the INDIRECT function returns the reference specified by a text string. These references are immediately evaluated to display their contents. The basic or common sse of the INDIRECT function is when you want to change the reference to a cell within a formula without changing the formula itself.
Function Syntax
INDIRECT(ref_text, [a1])Function Arguments
The INDIRECT function syntax has the following arguments:
- Ref_text (Required) A reference to a cell that contains an A1-style reference, an R1C1-style reference, a name defined as a reference, or a reference to a cell as a text string. If ref_text is not a valid cell reference, INDIRECT function returns the #REF! error value.
- If ref_text refers to another workbook (an external reference), the other workbook must be open. If the source workbook is not open, INDIRECT returns the #REF! error value.
- If ref_text refers to a cell range outside the row limit of 1,048,576 or the column limit of 16,384 (XFD), INDIRECT returns a #REF! error. Note: This behavior is different from Excel versions earlier than Microsoft Office Excel 2007, which ignore the exceeded limit and return a value.
- A1 (Optional) A logical value that specifies what type of reference is contained in the cell ref_text.
- If a1 is TRUE or omitted, ref_text is interpreted as an A1-style reference.
- If a1 is FALSE, ref_text is interpreted as an R1C1-style reference.
Some Examples
Please refer to the data shown in the screen shot below:
Now, let us insert a formula in the following cells:
A10: =INDIRECT($A$2)
A11: =INDIRECT($A$3)
A12: =INDIRECT($A$6)
A13: =INDIRECT("B"&$A$8)
With the formula above, see the results below:
Please note that the cell B6 is named “Jeff” causing the value 45 to display in cell A12. If cell B6 is not named, A12 will show the #REF! error value. See screen shot of the named cell B6:
Another Examples
- Start Excel.
- In Book1, Sheet1, cell A1 type: This is a result of an INDIRECT function.
- In Microsoft Office Excel 2003 and in earlier versions of Excel, click New on the File menu, click Workbook, and then click OK. In Microsoft Office Excel 2007, click the Microsoft Office Button, click New, and then click Create.
- In Book2, Sheet1, cell A1 type: Book1.
- In Book2, Sheet1, cell A2 type Sheet1.
- In Book2, Sheet1, cell A3 type A1.
- Save both workbooks as Book1 and Book2 respectively.
- In Excel 2003 and in earlier versions of Excel, type the following formula in Book2, Sheet1, cell B1:
=INDIRECT(“‘[“&A1&”.xls]”&A2&”‘!”&A3)In Excel 2007, type the following formula:
=INDIRECT(“‘[“&A1&”.xlsx]”&A2&”‘!”&A3) - The formula returns “This is a result of an INDIRECT funtion.“
NOTE: Tis only works when both Book1 and Book2 files are open and are saved in the same folder.
For sample Excel workbooks, download the files listed below.