The INDIRECT function in Excel is a versatile tool that returns a reference specified by a text string. This function allows you to create dynamic references to cells, ranges, or even other worksheets, making it a powerful feature for creating flexible and interactive spreadsheets. Whether you're managing large datasets, summarizing data from multiple sheets, or building dynamic models, INDIRECT can simplify and enhance your workflow.
Syntax
Here’s the syntax for the INDIRECT function:
| Function | Syntax | Description |
|---|---|---|
| INDIRECT | INDIRECT(ref_text, [a1]) | Returns the reference specified by a text string. The reference can point to a cell, a range of cells, or even a different sheet. |
ref_text: A text string that represents the cell or range reference (e.g., "A1", "Sheet2!B10").[a1](optional): A logical value determining the style of reference. IfTRUEor omitted,ref_textis treated as an A1-style reference. IfFALSE,ref_textis treated as an R1C1-style reference.
Example
Consider a scenario where you want to sum values from different sheets dynamically, depending on the name of the sheet provided in a specific cell.
| Sheet Name | Cell Reference | Value | Formula | Result |
|---|---|---|---|---|
| Data1 | A1 | 100 | =INDIRECT("'"&A1&"'!"&B1) | 100 |
| Data2 | B2 | 200 | =INDIRECT("'"&A2&"'!"&B2) | 200 |
| =SUM(INDIRECT("'"&A1&"'!"&B1), INDIRECT("'"&A2&"'!"&B2)) | 300 |
In this example, the INDIRECT function dynamically constructs references to cells in different sheets based on the sheet names provided in cells A1 and A2. The SUM function then adds these values together.
Practice Exercise 1
Task: Use the INDIRECT function to dynamically reference cells from different sheets and calculate the total sum.
| Sheet Name | Cell Reference | Value |
|---|---|---|
| Sheet1 | A1 | 150 |
| Sheet2 | B1 | 250 |
Practice Exercise 2
Task: Use the INDIRECT function to create a dynamic reference based on a user-specified sheet name and cell address.
| Input Cell | Sheet Name | Cell Reference |
|---|---|---|
| A2 | Sheet1 | A1 |
| A3 | Sheet2 | B2 |
Solution for Exercise 1:
- The formula in the Result Cell:
- Formula:
=SUM(INDIRECT("Sheet1!A1"), INDIRECT("Sheet2!B1")) - Explanation: This formula sums the values in
Sheet1(Cell A1) andSheet2(Cell B1) by dynamically referencing these cells using theINDIRECTfunction. - Result:
400
- Formula:
Solution for Exercise 2:
- The formula in the Result Cell for Sheet1:
- Formula:
=INDIRECT("'"&A2&"'!A1") - Explanation: The formula dynamically references cell A1 in the sheet specified by the value in Cell A1 (e.g., if A1 contains "Sheet1", it references "Sheet1!A1").
- Result: The value in the referenced cell (e.g., if Sheet1!A1 contains 150, the result will be
150).
- Formula:
- The formula in the Result Cell for Sheet2:
- Formula:
=INDIRECT("'"&A3&"'!B2") - Explanation: This formula works similarly to the one above but references cell B2 in the sheet specified by the value in Cell A2.
- Result: The value in the referenced cell (e.g., if Sheet2!B2 contains 200, the result will be
200).
- Formula:
These exercises demonstrate how the INDIRECT function can be used to create flexible and dynamic references within Excel, enabling you to work more efficiently with multiple sheets and changing data.