Create a dynamic reference to a named range

Let’s take a look.

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

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

To do this, I’ll use theCOUNT,SUM, andAVERAGEfunctions.

To start off, I’m going to hardcode the range for January, which is C6:C10.

That’s where the INDIRECT function comes in.

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.

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.

To make selecting the month more convenient, you’re free to add Data Validation to C13.

The Data Validation will then provide a drop-down to select each month in the table.