Quick, clean, and to the point

Create a dynamic reference to a named range

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 4 month period.

What were going to do is use the INDIRECT function to create 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 to this, we'll use the count, sum, and average functions. To start out, I'm going to hardcode the the range for January, which is C6:C10.

So, this works fine, but what if you want to be able to easily change the month?

That's where the INDIRECT function is comes in.

To quickly illustrate how INDIRECT works, I can type the range C6:C10 into cell C13, 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 numbers for February.

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

So, 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.

We can use Excel's create names feature to create all names at once. This creates a named range for the data under each month.

Now I can simply replace the range with a range name and the formulas work.

If I use F9 to evaluate indirect, you can see that it is correctly returning all values for the month.

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

Just select C13, 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.

Be sure that the names you are using for data validation match the named ranges being processed by INDIRECT.


Related shortcuts

Dave Bruns