Using Data Validation – The Trigger Technique

If you don’t know about data validation yet, I suggest you read the following document before getting familiar with this method.

http://www.contextures.com/xlDataVal01.html

Data validation is certainly one of the best tools to perform what-if analysis, along with sensitivity tables. There many types of sensitivity analysis that can be performed with data validation. These can be used alone or in combinations.

• Triggers
• Multipliers
• Delays
• Option lists
• Multiple inputs scenarios

Today, I’m going to show you the simplest one, the “On / Off” trigger. In the following example, I will use it to turn on or off certain product lines in a financial statement. As mentioned in an earlier post on best practices, I suggest you clearly identify the trigger cells with dashed borders.

The first step is to set the data validation. Select the cells where you want to locate the triggers and click “Data Validation”.

In Excel 2003 you can find it in the Data Menu.

In Excel 2007, it is located in the Data Tools section of the Data Tab.

Select List in the first drop-down menu and enter “On, Off” in the Source section.

You might also want to add indications to the user in the Input Message tab or control what happens when invalid data is entered in the Warning tab. In this case, the resulting drop-down menu is probably self-explanatory enough.

With that set, you only need to multiply the existing formula with the following:

…*IF(Switch Address="On",1,0)

Now, changing the switch on or off will automatically adjust the results

This method could also be used to show some comments or text, or in conjunctions with conditional formats, highlight some cells.

For an example, see picture below:

I used this formula in the resulting cell:

=IF(Switch Address="On","Light is On","")

The next picture shows you how I set the conditionnal format.