The most common way of creating input cells that are “idiot proof” is to use Data Validation – literally creating a “drop down” box of inputs and ONLY those inputs.

Sometimes, Microsoft wants to save users from themselves and sometimes, you need to develop a template or a spreadsheet that does exactly the same thing when used by others.

The most common way of creating input cells that are “idiot proof” is to use Data Validation – literally creating a “drop down” box of inputs and ONLY those inputs.

However, what if you want to create a data validation list that is dependent upon a selection made in another data validation list?

Believe it or not, this is possible.

Suppose you would like to set up the worksheet so that cell “A1” is a data validation list to select a customer from a list of customers.  You would them like the validation list in cell “B1” to update with a different list depending on what the user selected in cell “A1”.

The easiest way to accomplish this task is the following:

1) Beginning at cell “E1”, set up a data table. This table will contain your product “matrix.” In the first cell of each column, indicate a category name, such as ‘Fruits’, ‘Vegetables’ and ‘Pasta’.

2) Under each heading in the product matrix, list the various products in the category.

3) Select the headings from the data table, such as “E1:G1”.

4) In the “Define Name” toolbox, enter a descriptive name such as “Choices”

 

Contingent Data Validation - Screen Shot 1 - Contingent Data Validation Lists in Excel - FIG Consulting Partners - Microsoft Excel Tips

5) Select the actual products in Column “E” EXCEPT for the heading (cell “E1”).

6) In the “Define Name” toolbox, enter a descriptive name  (“Fruits”) THAT EXACTLY MATCHES THE NAME USED AS THE COLUMN HEADER (very important!!).

Contingent Data Validation - Screen Shot 2 - Contingent Data Validation Lists in Excel - FIG Consulting Partners - Microsoft Excel Tips

7) Repeat steps 5 through 7 for the other product columns (columns “F” and “G”).

8) Select cell “A1”.

9) Select “Data Validation”

10) Using the Allow drop-down list, choose List.

11) In the Source box, enter an equal sign followed by the name you defined in step 4 (such as =Choices).

 

Contingent Data Validation - Screen Shot 3 - Contingent Data Validation Lists in Excel - FIG Consulting Partners - Microsoft Excel Tips

12) Select cell B1 and again display the “Data Validation” dialog box.

13) Using the Allow drop-down list, choose List.

14) In the Source box, enter the following: =INDIRECT(A1).

 

Contingent Data Validation - Screen Shot 4 - Contingent Data Validation Lists in Excel - FIG Consulting Partners - Microsoft Excel Tips
15) Click OK. Excel displays a dialog box asking if you really want to use the rule. (This is because the referenced cell, “A1” currently resolves to an error condition.)

16) Click Yes.

That’s it.  Now, whatever is chosen in cell “A1” dictates which list is presented in cell “B1”.

 

Contingent Data Validation - Screen Shot 5 - Contingent Data Validation Lists in Excel - FIG Consulting Partners - Microsoft Excel Tips