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.
Dave Bruns
Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
