Indirectly to the Point

Using Named Ranges in your work in Excel is a highly recommended Best Practice. A problem may arise when trying to refer a Named Range, however. The solution is mastering the easy-to-use Indirect Function! 

If you read this blog occasionally, you know that I am big fan of Interactive Reports. Using the Indirect Function, along with drop-down boxes created with Validation, is another very cool way of making reports of this type.

 As I discussed in this blog several years ago, there are many uses for the Indirect Function. Used in its simplest form it simply take the Name in the cell it is referencing and returns the Named Range that it refers to.

 Try Creating a Small Spreadsheet Similar to the Graphic Above, and Try the Following: 

 1) Create your Named Ranges by selecting each of your ranges and typing the new Name in the Name Box on the upper-left corner of your worksheet. In the above example graphic, Name each of the monthly rows (excluding the cells with the month name) for the Units shown. For instance, for August, select C12:F12 and name it August (clever name, eh?).

 2) Then go to Data Validation in Tools and choose Allow List (choose the list of items that you want to appear in the drop-down box). For example, in the above graphic, the cell C2 with “April” in it contains the interactive drop-down created using the list of months.

 3) Now for the Good Stuff! In cell D2 insert the formula“=SUM(INDIRECT(C2))”. The Indirect Function reads the name of the month you choose with the drop-down box in C2 and the formula sums up the total for the units.

 Using the Indirect Function, along with Validation, is an easy way to make a truly powerful interactive report. How Cool is That!