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.
The Excel INDIRECT function returns a valid reference from a given text string. Use INDIRECT when you need to convert a reference assembled as text into a proper reference.
The Excel COUNT function returns the count of values that are numbers, generally cells that contain numbers. Values can be supplied as constants, cell references, or ranges.
The Excel SUM function returns the sum of values supplied. These values can be numbers, cell references, ranges, arrays, and constants, in any combination. SUM can handle up to 255 individual arguments.
The Excel AVERAGE function returns the average of values provided. AVERAGE can handle up to 255 individual arguments, which can include numbers, cell references, ranges, arrays, and constants.
Get over 200 Excel shortcuts for Windows and Mac in one handy PDF.
Quick, clean, and to the point.