Indirect and Validation

Interactive reports are powerful tools that can provide the user with multiple sets of information in a One-Stop, Professional Format. There are many ways to create reports of this type, (background pivot tables, Boolean functions, etc), but one of the easiest is to make use of the functionality in Validation combined with the Indirect Function.

Although there are many uses for the Indirect Function, used in its simplest form it will take the Word in the cell it is referring to and return the Named Range.
Using the small spreadsheet in the graphic above, 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 four units. For instance, for August, select C12:F12 and name it August.

2) To get a convenient Dropdown Box, go to Data Validation in Tools and choose Allow List (you can choose the list of items that you want to appear in the dropdown box). For example, in the above graphic, the cell C2 with “April” in it contains the interactive dropdown created using the list of months.

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

Try this straightforward, easy way to make an interactive report. They’ll soon put up statues in your honor!