This tutorial will show you how to use INDIRECT to create dynamic references that adapt to your data.

What is the INDIRECT Function?

The INDIRECT function takes a text string and turns it into a valid cell reference. It lets you build formulas that can change dynamically based on other inputs.

Here’s the syntax:

=INDIRECT(ref_text, [a1])

• ref_text: This is the text string that represents a cell reference, like “A1” or “Sheet2!B5”.
• [a1]: This is optional. If you set it to TRUE (or leave it blank), Excel uses the standard A1-style reference. If you set it to FALSE, it uses the R1C1 style, which is less common.

The magic of INDIRECT is that it lets you create formulas that aren’t tied to a specific cell or sheet. Instead, they can adapt as your data changes.

Example 1: Dynamically Referencing Sheets Based on User Input

Imagine you have monthly sales data stored in separate sheets—let’s say Jan and Feb. You want to create a summary sheet where someone can select a month from a dropdown, and Excel will automatically show the total sales for that month. Here’s how you can do it with INDIRECT.

Step 1: Set Up the Data

Step 2: Create a Dropdown List
Let’s create a dropdown list in cell A1 with the months Jan, Feb. This will let users select which month’s data they want to see.

Step 3: Sum Sales Dynamically
Next, in cell B1, we’ll use INDIRECT to sum the sales for the selected month. Here’s the formula:

=SUM(INDIRECT("'" & A1 & "'!B2:B4"))

Let me break this down for you:

• A1 is the cell with the dropdown (e.g., Jan).
• “‘” & A1 & “‘!B2:B4” builds a text string like ‘Jan’!B2:B4.
• INDIRECT turns that text string into a valid reference, and SUM adds up the values in that range.

So, if you select Jan from the dropdown, the formula becomes =SUM(‘Jan’!B2:B4), and it calculates the total sales for January.

Example 2: Building a Dynamic Named Range

Imagine you have a list of products in Excel, and this list keeps growing over time—maybe you’re adding new products every week or month. Now, if you’re using this list in a dropdown or a formula, you’d have to manually update the range every time you add a new product. That’s not only tedious but also prone to errors.

What if I told you there’s a way to make this range automatically expand as you add new entries? That’s exactly what a dynamic named range does, and we’ll use the INDIRECT function to make it happen.”

First, let’s set up our data. Here’s what our product list looks like in Sheet3:

Let’s say this list starts in cell A2 and goes down. As you add more products, the list grows. Our goal is to create a named range that automatically includes all the products, no matter how many you add.

Now, let’s create the dynamic named range. Here’s how you do it:

• Go to the Formulas tab on the Excel ribbon and click on Name Manager.

• In the Name Manager window, click New to create a new named range.

• In the Name field, type ProductList. This will be the name of our dynamic range.

• In the Refers to field, enter this formula:

=INDIRECT("'Sheet3'!A2:A" & COUNTA(Sheet3!A:A))

Let me explain what this formula does:

• COUNTA(Sheet3!A:A) counts all the non-empty cells in column A. So, if you have 10 products, it returns 10.
• “Sheet3!A2:A” & COUNTA(Sheet3!A:A) builds a text string like Sheet3!A2:A11 (if there are 10 products, starting from row 2).
• INDIRECT takes this text string and turns it into a valid range reference.

So, this named range will always include all the products in column A, no matter how many you add.

Now that we’ve created the dynamic named range, let’s see how to use it. One common use case is creating a dropdown list that automatically updates as you add new products.

Here’s how to do it:

• Select the cell where you want the dropdown (e.g., cell B2).

• Go to the Data tab and click on Data Validation.

• In the Data Validation window, under Allow, select List.

• In the Source field, type =ProductList (the name of our dynamic range).

• Click OK.

Now, you have a dropdown that includes all the products in your list. And here’s the best part: if you add a new product to column A, the dropdown will automatically include it. No manual updates needed!

Let’s test this to make sure it works. Go back to your product list in Sheet3 and add a new product, like Mango, at the bottom of the list.

Now, go back to your dropdown in cell B2. You’ll see that Mango is now included in the dropdown. That’s the power of a dynamic named range! It saves you time and ensures your data stays up-to-date.

They’re especially helpful in templates or dashboards where the data is constantly evolving. By using INDIRECT to create dynamic ranges, you save time and reduce the risk of errors.”

Video: