Search Contextures Sites

Excel Data Validation -- Create Dependent Lists With INDEX

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

Create Named Lists
Create the Dynamic Ranges
Set up Validation for Column A on Data Entry
Setup Validation for all other columns on Data Entry  
Finished Data Entry sheet with sample Data  
Expanding Columns on sheet Lists  

 

Download a sample
Excel data validation file


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

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
    First named range

 

 

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 the 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

Dependent two word lists

 

  

Setup the Validation cells for all other columns on Data Entry

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

 

 

The finished Data Entry sheet with sample Data

 

 

 

 

 

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.

 

 

Download a sample
Excel data validation file

 

 
Download 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.

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

 


Roger Govier

Excel Tutorials - Data Validation

Data Validation Basics
Data Validation - Create Dependent Lists
Data Validation - Dependent Dropdowns from a Sorted List  
Data Validation - Dependent Lists With INDEX  
Hide Previously Used Items in a Dropdown List
Data Validation - Display Messages to the User
Data Validation - Display Input Messages in a Text Box 
Data Validation - Use a List from Another Workbook
Data Validation Criteria Examples
Data Validation Custom Criteria Examples
Data Validation Tips
Data Validation Documentation
Data Validation Combo Box     
Data Validation Combo Box - Named Ranges
Data Validation Combo Box -- Click
Data Validation - Add New Items  

Learn how to create Excel dashboards.

 

       Home     Excel Tips     Excel Files     The Excel Store     Blog     Contact

 

Privacy Policy

 

Contextures Inc., Copyright ©2010
All rights reserved.