Summary
The INDIRECT function in Excel is used to convert a text string into a cell reference. This function is incredibly useful for creating flexible cell references that can change dynamically based on the content of another cell. INDIRECT can be used for referencing cells indirectly, creating dynamic ranges, or working with data across different sheets dynamically.
Syntax
=INDIRECT(ref_text, [a1])
- ref_text: A text string that represents a cell reference or a range.
- [a1]: [Optional] A logical value that specifies what type of reference is contained in the
ref_textstring.TRUEor omitted indicates A1-style reference.FALSEindicates R1C1-style reference.
Return value
Returns the reference specified by a text string.
How to use
Use INDIRECT by providing a text string that represents a cell reference. The function will interpret the text and return a reference to the specified cell or range. This allows for dynamic cell referencing based on the content in other parts of the worksheet.
Examples
Simple INDIRECT
Dynamic Reference to a Cell: To reference a cell dynamically based on another cell’s content:
=INDIRECT("A" & B1)
Assuming B1 contains the number 5, this formula constructs a cell reference to A5 and returns the value in that cell.
INDIRECT with Named Ranges
Using INDIRECT with Named Ranges for Dynamic Referencing: To create a dynamic reference to a named range based on the selection in another cell:
=INDIRECT(C1)
If C1 contains the name of a named range, such as “SalesData”, this formula will return the reference to the named range “SalesData”.
INDIRECT Across Worksheets
Referencing Data Across Different Worksheets: To reference a cell in a different worksheet dynamically:
=INDIRECT("'" & D1 & "'!B3")
If D1 contains the name of a worksheet, say “Sheet2”, this formula constructs a reference to cell B3 on “Sheet2”.
INDIRECT in Data Validation
Creating Dynamic Dropdown Lists with INDIRECT: To create a dropdown list in data validation that changes based on another cell’s value, in the data validation formula field use:
=INDIRECT(E1)
Assuming E1 contains the name of a range, this data validation rule will create a dropdown list with values from the range named in E1.
INDIRECT for Dynamic Range in SUM
Dynamic Range Summation Using INDIRECT: To sum a range that dynamically changes based on input in another cell:
=SUM(INDIRECT("A1:A" & F1))
If F1 contains the number 10, this formula sums the range A1:A10. As the value in F1 changes, the summed range adjusts accordingly.
Additional Notes
- INDIRECT is a volatile function and can cause performance issues in large or complex worksheets.
- It can be combined with other functions for dynamic lookups, range manipulations, and cross-sheet references.
- INDIRECT is useful for scenarios where the structure of a spreadsheet changes frequently, allowing formulas to adapt dynamically.