Variable Drop Down Menu / Data Validation (DV) List

Objective

In a model you need multiple drop down menus, the choice in drop down menus is dependent upon each other. Like if ones chooses Asia (in 1st menu), then only Asian Countries be available as second choice (2^nd menu); for Europe only European countries be prompted.

Prerequisites

Basic understanding of Data Validation &; INDIRECT function.

Solution Concept

In data validation ‘List’ is created from specific cells like A4:A10. However with help of INDIRECT function we can change the reference cells and thus choices.

In sample example: if Continent is chosen as ‘Asia’ then second drop down ‘List’ shall refer to Asian countries table (e.g. Sheet = Asia, Cells A10:A36); if continent is Europe then second drop down ‘List’ shall refer to European countries table (e.g. Sheet = Europe, Cells C15:C50).

The reference can be changed with help of INDIRECT function. Data validation in all cases will refer to a specific cell say “D2”, however contents of D2 will change whenever Continent is changed.

Let’s do it

1.1.      Basic Data Validation

1.2.      Creating second list with Indirect Function

1.2.1. Steps that will follow Basic Data Validation rules:

·         Selecting Cell(s) for applying Data Validation

·         Invoking Data Validation from Data menu/ribbon

·         Selecting ‘List’ from Data Validation pop-up window

1.2.2. In the Source Field, you need to use equation with INDIRECT function instead of direct cell reference, like ‘Indirect(D1)’ instead of ‘A1:A3’.

1.2.3. The key is Cell D1, whenever cell reference appearing in D1 changes, the choice of dropdown menu automatically changes.

1.2.4. In reference to sample workbook (link below):

Please note the ‘Yellow’ highlighted Cells

1.2.5. These cells contain cell references to another sheet &; they change if first choice i.e. continent changes. See the difference between above and below pictures with change of continent from Asia to Europe

1.2.6. Now have a look at Data Validation references:

The First Option is simple, whereas the other two use INDIRECT Function

1.2.7. Conceptual explanation; How Yellow Cells are updated:

  

   Have a look at 'Summary' Sheet:

In this sheet Cell ranges are already TYPED, with help of MATCH, CHAR and INDIRECT functions these ranges are called in ‘DValid’ sheet and then joined with Sheet names with help of ‘&’ operator (You can also use CONCATENATE function).

1.2.8. To get full grip on the concept, you may need to review the model in attached sample workbook completely.

You can start by reviewing simple use of INDIRECT validation in ‘Simple DV with Indirect’ Sheet, before reviewing bigger model at ‘Dvalid’

Sample Workbook