The INDIRECT function allows you to use a cell reference entered as a text string. This can be used to:
- Ensure the reference does not change when additional rows and columns are inserted in the worksheet
- Create a reference from letters and numbers entered into other cells. This also enables you to change the reference in the formula without editing the formula itself
- Refer to a named range. Very useful when used with Data Validation to create dependent drop down lists
The syntax for the INDIRECT function is:
=INDIRECT(ref_text, [a1])
| Argument | Purpose |
|---|---|
| ref_text | The referenced range. Can be entered as a cell reference, text string that creates the cell reference or a range name |
| a1 | The type of reference used in ref_text. True, or omitting the argument, uses the A1 style. False uses the R1C1 style |
The example below demonstrates the INDIRECT function being used to create a cell range using a text string. Using this method allows us to alter the function by changing cells E1 and E2 as opposed to changing the function itself. It also locks the cells so the function will not change when new rows are added.
The example below demonstrates the INDIRECT function being used to return the total for the South region. The value in cell E1 refers to a named range and the INDIRECT function allows us to pass this to the SUM function
