Take the Indirect Route

Named Ranges can be a terrific boon to the efficiency and ease-of-use of your Excel workbooks. Although using named ranges is one of my favorite Best Practices, curious problems may arise when referring to these effective shortcuts. To obtain true mastery in this regard, however, the savvy Excel Guru can employ the humble Indirect Function! 

Used in its simplest form, the Indirect Function can simply take the Name in the cell it is referencing and return the Named Range that it refers to. Using the Indirect Function along with drop-down boxes created with Validation, is a very user-friendly way of making Interactive Reports.

Create a Spreadsheet Like the One Shown Above, and then: 

 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 for the Units shown. For instance, for December, select C15:G15 and name it December (no sense in making this any more difficult than that…).

2) In cell D18 insert the formula “=SUM(INDIRECT(C18))”. The Indirect Function reads the name of the month you choose with the drop-down box in C18 and the formula sums up the total for the units.

Using the Indirect Function along with Validation it is just one example of how to use this powerful tool in Excel. I wish you all a very Happy and Successful New Year