Let’s look at three basic ways to use the Excel INDIRECT function right away. This function can create dynamic references that automatically update when the location of referenced cells and ranges changes.
3 Ways to Use the Excel INDIRECT Function
It is a reference function that indirectly refers to a cell or cell range based on a text string.
Dynamic Cell Range References
The first way to use the INDIRECT function is to dynamically reference cell ranges.
The table below contains sales quantities by day of the week.
Each column contains sales quantities for Monday through Friday.
First, set a range name for each weekday column.
A quick overview of how to name ranges in Excel is below.
- Select the range you want to name.
- Enter the desired name in the Name Box in the upper-left corner.
Range names have been assigned to columns C through G, as shown below.
Finally, use the following formula to create weekday totals for the dynamic ranges with the Excel INDIRECT function.
The Excel formula is as follows.
=SUM(INDIRECT(I3))
Adding Cell Values Across Multiple Sheets
As shown below, four Excel worksheets contain sales quantities by weekday for each week.
We will analyze the values from these four Excel worksheets in a worksheet named “SUM INDIRECT” using the following table.
The weekday cells in “DAY” and product cells in “PRODUCT” use the Excel list feature, as shown below.
In summary, use the INDEX MATCH function in the table above, then use the INDIRECT function to convert the names of the four Excel worksheets into references.
First, in cell C, use the INDEX MATCH function to retrieve values from another worksheet, using the weekday and product as the two criteria.
Next, in cell D, use the INDIRECT function to convert the names of the four Excel worksheets into sheet references.
The key function for converting an Excel worksheet name into a range reference is as follows.
The INDIRECT function also works well in combination with the INDEX MATCH function and the SUMIFS function.
Dynamic Drop-Down Lists
When you use the “Excel list feature” and “range naming” feature used above together with the INDIRECT function, you can make a list change automatically based on conditions.
As shown below, “Zone” contains A and B. Zone A consists of {1,2,3,4,5}, while Zone B consists of {one,two,three,four,five}.
Let’s look at how to create a feature where the Excel list changes automatically using the INDIRECT function based on the selection of A or B.
First, name each range to be used in the list.
As shown below, select A or B in OPTION1, and have the list in OPTION2 automatically change to the A or B list based on the OPTION1 selection.
For the Option1 list, use the named range below.
For the Option2 list, use the named range below.
Create the list using the INDIRECT function for the Option1 result and an absolute reference.
After completing the steps above, a list that automatically changes based on each selection is created, as shown below.
Changing Excel Range Names
When naming Excel ranges, you may need to delete a range name or change a range.
As shown below, you can do this using Name Manager on the Formulas tab.
Conclusion
We explored three efficient ways to reference ranges using the Excel INDIRECT function. By using named cell range references and worksheet name references, you can further automate your Excel workbooks.