The Excel INDIRECT function is a powerful tool that allows users to dynamically reference cells and ranges within a worksheet. This function can be particularly useful when you need to refer to a cell or range of cells that you don’t know the location of until runtime. INDIRECT can help you to create more flexible and dynamic formulas that can adapt to changes in your data.
Excel INDIRECT Function Summary
Excel’s INDIRECT function allows you to dynamically reference a cell by combining text strings to create a valid cell reference. This eliminates the need to manually update formulas and simplifies the process of changing the reference to a cell. Unlike other functions, INDIRECT does not evaluate logical tests or conditions, making it ideal for use when referencing changing cell locations within a formula.
INDIRECT Function Purpose
The purpose of the INDIRECT function is to convert a text string into a cell reference. This can be useful when you want to reference a cell or range of cells whose location is based on the value of another cell, or when you want to build a reference to a cell programmatically.
INDIRECT Function Arguments
The INDIRECT function has only one required argument – the text string that represents the cell reference you want to create. You can also use optional arguments to specify the type of reference you want to create, such as A1 or R1C1 style references.
INDIRECT Function Return Value
The INDIRECT function returns a cell reference that is based on the text string provided as the argument. This reference can then be used in other formulas or functions.
INDIRECT Function Syntax
=INDIRECT(ref_text, [a1])Excel INDIRECT Function Examples
Excel’s INDIRECT function is a powerful tool that allows you to create dynamic references to cells, ranges, and other objects within a worksheet. By using the function, you can change the reference to a cell within a formula without having to manually update the formula itself. Below are some examples of how you can use INDIRECT to create flexible and dynamic formulas in Excel.
Using INDIRECT to Reference a Cell Indirectly
In some cases, you may want to reference a cell in a formula indirectly. The INDIRECT function can be used to accomplish this by returning a reference to a cell based on a text string.
By using the following formula:
=INDIRECT(A2)
Explanation: In this example, cell A2 contains the text string “B3”. The INDIRECT function then returns the value of the cell referenced by this string, which is cell B2.
Using INDIRECT with the SUM Function
The INDIRECT function can also be used with other functions, such as the SUM function, to dynamically reference cells based on changing criteria.
By using the following formula:
=SUM(INDIRECT(A1&":"&A2))
Explanation: In this example, cell A2 contains the text string “B3” and cell A3 contains the text string “B5”. The INDIRECT function concatenates these strings with a colon (:) to create a range reference, which is then used as an argument in the SUM function. This formula returns the sum of the values in cells B2 through B5.
Using INDIRECT to Create Dynamic Named Ranges
Named ranges in Excel can be useful for simplifying formulas and making them more readable. However, named ranges are usually static and cannot be easily modified. The INDIRECT function can be used to create dynamic named ranges that can be updated based on changing criteria.
By using the following formula:
=INDIRECT("'"&A1&"'!"&A2)
Explanation: In this example, cell A1 contains the name of a worksheet, and cell A2 contains a cell reference on that worksheet. The INDIRECT function concatenates the worksheet name with an exclamation mark (!) and the cell reference to create a valid cell reference. This formula returns the value of the cell specified by the dynamic named range.
Using INDIRECT with the SUM Function + Named Ranges
Now, lets say we use the sample sample set as above but this time we don’t want to return a string of number. We want to total the named range.
If you want to SUM the named range you could use the following function:
=SUM(INDIRECT("'"&A2&"'!"&A3))
As you can see unlike our last example were all the sales were returned. This time we get the total sales of 242.
INDIRECT Function Notes
- The INDIRECT function can be volatile, meaning it can slow down your worksheet if used excessively.
- It is important to ensure that the text string provided as an argument is a valid cell reference. Otherwise, the function will return a #REF! error.
- The INDIRECT function can be used in a variety of contexts, including data validation, conditional formatting, and charting.