The INDIRECT function in Excel is used to reference a cell or a range of cells indirectly. This means you can change the reference to a cell within a formula without changing the formula itself. The INDIRECT function is especially useful when you want to create dynamic references, allowing references to update based on the value of another cell.
Here’s the syntax for the INDIRECT function:
INDIRECT(ref_text, [a1])
- ref_text: This is a reference to a cell containing an A1-style or R1C1-style reference, or it can be a reference string itself. This argument is required.
- a1: This optional argument is a logical value that specifies what type of reference is contained in the ref_text argument:
- If `TRUE` or omitted, INDIRECT treats ref_text as an A1-style reference.
- If `FALSE`, INDIRECT treats ref_text as an R1C1-style reference.
Basic Example
Suppose you have a cell (say, B1) that contains the text `A1`, and in cell A1, you have the value 100. If you use the INDIRECT function in another cell like this:
=INDIRECT(B1)
Excel returns 100, because it reads the contents of B1 (`A1`) and uses it as a cell reference, hence retrieving the value from A1.
Dynamic Range Example
If you want to create a dynamic range based on another cell’s value, you could do something like this:
=SUM(INDIRECT("A1:A" & B1))
- Assume you have values in cells A1:A10.
- In cell B1, you type the ending row number for your range, such as `5`.
- You want to sum the range from A1 to A5, based on the value in B1.
- You would write a formula using INDIRECT like this:
This formula dynamically changes the sum range based on the number you put in B1.
Referencing Different Worksheets
You can also use INDIRECT to refer to cells on different worksheets by concatenating a sheet name with a cell reference.
Let’s say you have a sheet named “DataSheet” and you want to refer to cell A1 on that sheet, but the sheet name is in cell B2. You would use:
=INDIRECT("'" & B2 & "'!A1")
This helps when working with multiple worksheets where sheet names might change or be determined programmatically.
Considerations
- Volatility: INDIRECT is a volatile function, meaning it recalculates every time any cell in the workbook changes, which might impact performance in large spreadsheets.
- Named Ranges: INDIRECT can also reference named ranges, allowing for dynamic manipulation of how named ranges are used in formulas.
By using INDIRECT, you allow your formulas to be flexible and adaptable, responding automatically to changes in the input or reference cells.
Unlock Your Potential
Excel
Access
Power BI
