Create Dependent Lists With INDEX

As an alternative to using INDIRECT to create dependent Excel data validation lists, you can use the non-volatile INDEX function.

NOTE: There are other techniques for setting up dependent drop down lists, so choose the setup method that is best for your needs.

Thanks to Roger Govier, who created this tutorial and sample file.

Introduction

In this example, just 4 dynamic range names are used. Of these, 3 are used to create the basic framework of the method.

The fourth permits any number of subsidiary lists to be created to act as Dependent dropdown lists from the entry in the previous column, without having to define individual names for each list.

Create Named Lists

1. In a new Workbook, rename Sheet 1 as Data Entry
2. Rename Sheet2 as Lists
3. Enter Headings on the Data Entry sheet in cells A1:D1 -- Region, Country, Area and City
   
   Data Entry Sheet
   
   
   
4. On the Lists sheet in cell A1 add the headings Regions.
5. In cells A2:A5 enter APAC, MEA, Europe and Americas.
   (These first 2 headings represent Asia & Pacific, Middle East & Africa)

   Lists Sheet
   

Create the Dynamic Ranges

Select sheet Lists and then go through the following processes to create the 4 Dynamic range names that are required:

• Master - which will be used for the validation in column A of Data Entry
• ValData - which will define the range on Sheet Lists that contains the Validation Data
• Counter - which will be used to determine the length of each Validation List
• UseList - which is used for all columns where validation is required on Sheet Data Entry, apart from column A

Create the First Dynamic Range

Our first List is going to be called Master

1. Choose Insert | Name | Define
2. Type a name for the first range -- Master
3. In the Refers To box, enter an Index formula that defines the range size, based on the count of items in column A of the Lists sheet:
      =Lists!$A$2:INDEX(Lists!$A:$A,COUNTA(Lists!$A:$A))
4. This will give a dynamic List starting with cell A2 and extending for as many entries as we create in column A

Add More Headings and Entries to Lists

Enter all the Region names as headings in cells B1:E1 of the Lists Sheet.

Then, under each Region heading, enter one or more countries from that region. (See screen shot below, or see the sample file)

Create the ValData Dynamic Range

On the Lists sheet is the range of data that will be used in all validations for the Data Entry sheet. Instead of referring to the sheet name as range for this data, which will grow as more validations are added, you'll create a Dynamic range.

1. Choose Insert | Name | Define
2. Type a name for the range -- ValData
3. In the Refers To box, enter an Index formula that defines the range size, choosing say 100 rows and a count of the columns on the sheet =Lists!$A$2:INDEX(Lists!$1:$100,100,COUNTA(Lists!$1:$1))
4. This will give a dynamic List starting with cell A2 and extending for 100 rows and as many entries as we create in Row 1
5. Click the Add button

The choice of 100 rows is arbitrary, and should just be sufficient to contain whatever is going to be the longest of your lists.

Next, because each individual List can have a different number of values, we need to create a Counter.

Create a Counter

The Counter is a variable which defines the length of each individual List. It is dynamic, and will alter dependent upon which column is being used, the column being returned by the Match function. A dynamic range will be created for the Counter.

Note: The range is relative, and refers to the cell to the left (A2) to determine its values, so it is vital that you have the cursor in cell B2 of the Data Entry sheet when defining the range.

1. On the Data Entry sheet, type Europe in cell A2 (this is a temporary value, and you can delete it later)
2. On the Data Entry sheet, select cell B2
3. Choose Insert | Name | Define
4. Type a name for the range -- Counter
5. In the Refers To box, enter an Index formula that defines the range size, based on the count of numbers in the relevant column:
   =COUNTA(INDEX(ValData,,MATCH('Data Entry'!A2,Lists!$1:$1,0)))
6. Click the Add button

Create the UseList Dynamic Range

This is the universal list name that will apply to all lists that are added to the sheet other than the Master list as previously described.

As new columns are added to the Lists sheet, their relevant used range is automatically dynamically created, and using UseList as the data source will apply to the whole of the validation areas on sheet Data Entry.

Note: Because the range is relative, and refers to the cell to the left (A2) to determine its values, it is vital that you have the cursor in cell B2 of the Data Entry sheet when defining the range.

1. Place your cursor in cell B2 of Data Entry sheet.
2. Choose Insert | Name | Define
3. Type a name for the range -- UseList
4. In the Refers To box, enter the following formula
   =INDEX(ValData,1,MATCH('Data Entry'!A2,Lists!$1:$1,0)): INDEX(ValData,Counter,MATCH('Data Entry'!A2,Lists!$1:$1,0))
5. Click the Add button

Why the Cursor Placement is Important

• ValData has previously been defined as a range beginning in Row 2 of the Lists sheet.
• The UseList formula indexes the Valdata range, with 1 fixed value for the starting row element, in the first part of the formula and 1 dynamic value for the ending row element, in the second part of the formula.
• The formula has 1 relative value for the column element of the Index in both parts of the formula.
• Row in the first part is determined by the 1 in (Valdata,1, ...) and will therefore refer to Row 1 of ValData (therefore, row 2 of the Lists sheet).
• Row in the second part is determined by Counter in (Valdata,Counter, ...) , and Counter is a previously defined count, which will vary according to the number of entries in the validation list for the relative column.
• Column is determined by the Match of whatever is in the cell to the left of the cell using this dynamic range, MATCH('Data Entry'!A2,Lists!$1:$1,0) with the values of the List headings held in row 1 of the Lists sheet.

That is why it is so important to place the cursor in the correct starting cell when creating this named range.

Setup Validation cells for Column A on Data Entry

Now that you've defined the dynamic ranges, the next step is to set up the validation ranges on the Data Entry sheet.

1. On the Data Entry sheet, select cells A2:A12 (or as many rows as you require)
2. Choose Data | Validation

   

3. Choose Allow | List
4. In the Source box, type =Master
5. Click OK

Setup Validation cells - other columns

The next step is to set up the remaining validation ranges on sheet Data Entry

1. On the Data Entry sheet, select cells B2:D12 (or as many rows as you require)
2. Choose Data | Validation
3. Choose Allow | List
4. In the Source box type =UseList
5. Click OK

Finished Data Entry sheet

Expanding Columns on sheet Lists

You can add as many extra columns as you wish on sheet Lists. Each column has to have a heading which is a member of one of the other columns on the sheet, so that the new column added will be Dependent upon that heading.

For example, you could add a heading for US, and type a list states in that column. Next, you could add the state names as headings in row 1, and type a list of city names for each state.

You do not need to define any new names, as the Uselist dynamic range name will automatically deal with that for you.

Get the Sample File

Get a sample Excel data validation file

About the Developer

Roger Govier is an Excel MVP based in the UK who undertakes assignments in Excel and VBA for clients worldwide. While he enjoys the intellectual challenge of solving problems with worksheet functions, Roger claims to be intrinsically lazy, so he always looks for a fast and simple way to provide solid workable solutions.

Find more of Roger's tutorials and sample files here: Sample Files - Roger Govier

You can contact Roger at:

roger@technology4u.co.uk
Technology 4 U, Glanusk Farm,
Llanfair Kilgeddin, Abergavenny, NP7 9BE, UK
Tel +44 (0) 1873 880266
Mobile +44 (0) 7970 786191

Related Tutorials

Data Validation Basics

Dependent Drop Down Setup Choices

Dependent Drop Down from Dynamic Arrays

Create Dependent Lists

Flexible Dependent Drop Downs

Dependent Dropdowns from a Sorted List