Create a dynamic reference to a named range (video)

Abstract

In this video we'll look at how to create a dynamic reference to a named range with the INDIRECT function. This is one way to switch ranges on the fly.

Transcript

In this video we'll look at how to create a dynamic reference to a named range using the INDIRECT function.

Let's take a look.

Here we have a simple table that summarizes sales by salesperson over a four-month period.

What we're going to do is use the INDIRECT function to make a dynamic reference to a named range that corresponds to each month. This is a useful technique when you want to provide an interactive way to change a reference on a worksheet.

First, let's set up a few formulas to get the basics working.

To do this, I'll use the COUNT, SUM, and AVERAGE functions. To start off, I'm going to hardcode the range for January, which is C6:C10.

You can see that this works fine, but what if you want to be able to easily change the month? That's where the INDIRECT function comes in.

To quickly illustrate how INDIRECT works, I can type the range C6:C10 into cell C13, and then adjust each formula to refer to C13 using the INDIRECT function.

The result is that INDIRECT evaluates the text in C13 and returns a reference to the actual range. If I now change the range to D6:D10, we'll get the numbers for February.

Note that the range must be valid; if not, INDIRECT will return a #REF error.

So, this works, but typing in a range isn't much fun, and it makes using this spreadsheet awkward. To create a nicer experience, let's create a named range for each month and then change INDIRECT to refer to the names, instead.

I can use Excel's "Create Names" feature to create all names at once. We now have a named range for the data under each month.

Now I can simply replace the range in C13 with one of the named ranges and the formulas will work.

If I use F9 to evaluate the INDIRECT function, you can see that it's correctly returning all the values for that month.

To make selecting the month more convenient, you can add Data Validation to C13.

Just select the cell, and set Data Validation to allow a list, then refer to the month names on the worksheet. The Data Validation will then provide a drop-down to select each month in the table.

When you use this approach, make sure the names you are using for Data Validation match the named ranges being processed by INDIRECT.