Data Validation – Creating Dependent drop down list in Excel

May 31st, 2011 by Tips Master | Posted under Excel, Excel Functions.

Data validation in ExcelAfter reading the last post, explaining on how to create a linked list using Excel data Tables, I am getting a lot of mails asking about creating dependent list using Indirect function. We will see today how to create a dependent list in Excel data validation using Indirect function. This is a very short and quick tutorial when the list is very small and does not contains any duplicates. If the list contains duplicate and lot of items , its suggested to use the method explained for avoiding the duplicates in Excel data validation.

 

 


 

Create named ranges

This is the first step in creating the dependent lists. We have the following list with the Region name and the respective country list.

 

Country List

We need to define the name for region using Define Name in Formula Ribbon. There is an easy option available to create name for selected range. To do this after selecting the List (including the heading ) use the option “Create from selection”

 

Define Name in Excel

We will use the name for the first list as “Region”. Make sure you have the checked the box Top row so that excel will create a named range with the name as Region.

 

Create name fron top row

Now in the cell where we require the first list select the data validation – List (Shortcut Alt+D+L)

Data Validation List in Excel

For the Source give the name as “Region”

 

Create Data Validation List

We have completed with our first data validation list.

 

Creating Dependent List

Now we will see how to work with our 2nd list which is linked to our first list.

We need to create some more ranged names, where the name will be the Region name

Named ranges for Country
Repeat the earlier steps to create 3 named ranges with the ranges as the country names.You can see the Named ranges in the Name manager below

 

Name manager in Excel

Using INDIRECT Function to link the Names

Now to create the data Validation for the Dependent list which is the country. Select the required cell and open the data validation Window and in the source give the following formula

=INDIRECT(SUBSTITUTE($C$4,” “,”_”))

where C4 contains the earlier name and Excel will considers the value of the name in cell C4.

We take the use of the indirect function to get the required name based on our initial selection in the region list. The substitute function is specifically required for the name North America were the name equal to the same will be North_America. This is because the Excel name will allow to crate Names with Blanks. So when we crate the name it will automatically create “_” instead of the space.

We have competed created the linked list and you can start using the same in your reports.

 

Data Validation list in Action

See below a screen shot to see the Linked list will in work

 

Data Validation in action

 

Download the Completed file

Use the link to download the completed file (version Excel 2007)  - Dependend List using indirect function

Use the link to download the completed file (version Excel 2003)  - Dependend List using indirect function

Don’t  forget to give your valuable comments


Related posts:

Excel Date - Converting in to Standard Date Format in Excel
Creating Dynamic Dashboards in excel using combo boxes and dynamic charts
Part 2 - Organize data using Excel tables

Comments

9 Responses to “Data Validation – Creating Dependent drop down list in Excel”
  1. Shabbir Malik says:
    June 1, 2011 at 6:50 pm

    I am sure there is a reason for writing this statement:
    =INDIRECT(SUBSTITUTE($C$4,” “,”_”))

    For years, I have been using this statement:
    =INDIRECT($C4,TRUE)

    I get the same result as the above statement. BTW, it is important to not absolute the row i.e., instead of “$C$4″ choose “$C4″ This way if you copy the formula down the indirect function validates all the input cells.

    Reply
    • Tips Master says:
      June 1, 2011 at 8:45 pm

      @ Shabbir Malik … first of all thanks for the tips.. There were certain reasons for using those in the formula. I will explain one buy one so it will benefit all the readers.
      1. =INDIRECT(SUBSTITUTE($C$4,” “,”_”)) this formula you are referring to is used specifically for getting the name for North America correctly. There is a space between the name (for North America) and Name in excel will not accept space in between, as a result it will create a “_” for the space. By substituting ” ” with “_” we are trying to get the correct name. It wont work by the method you suggested. In the normal cases you don’t even require “True” because leaving it will give the default value as true which is for selecting referencing styles.
      2. Absolute reference is used for Rows and columns because it is the best practice if you use inside names or Data validation so that it will prevent from using relative referencing in Excel. Here it is not possible to drag the formula to create relative reference. You are absolutely correct if it is used inside a cell.

      Reply
    • Tips Master says:
      June 2, 2011 at 9:18 pm

      @ Shabbir Malik … first of all thanks for the tips.. There were certain reasons for using those in the formula. I will explain one buy one so it will benefit all the readers.
      1. =INDIRECT(SUBSTITUTE($C$4,” “,”_”)) this formula you are referring to is used specifically for getting the name for North America correctly. There is a space between the name (for North America) and Name in excel will not accept space in between, as a result it will create a “_” for the space. By substituting ” ” with “_” we are trying to get the correct name. It wont work by the method you suggested. In the normal cases you don’t even require “True” because leaving it will give the default value as true which is for selecting referencing styles.
      2. Absolute reference is used for Rows and columns because it is the best practice if you use inside names or Data validation so that it will prevent from using relative referencing in Excel. Here it is not possible to drag the formula to create relative reference. You are absolutely correct if it is used inside a cell.

      Reply
  2. Will says:
    June 21, 2011 at 8:05 pm

    Hi, thank you for your tutorial and demo data,

    Just a quick question – when you change the region, the country below doesnt automatically update to show the latest “selection options”.

    Eg. I select Asia, choose India as my country, then change the region to Europe, but India stays in the Country selection.

    Anyway to make it change to whatever is at the top of Europe’s list? “Austria” in this case.

    Thanks again,

    Will

    Reply
    • Tips Master says:
      June 24, 2011 at 8:08 am

      @ Will Thanks for the comments.. I also had a look in to the same when i prepared the work book. Sorry to say this is not possible without VBA. I was trying to explain this without the help of VBA. I will be soon coming with a post to give a solution using VBA.

      Reply
    • Tips Master says:
      June 24, 2011 at 8:12 am

      @ Will Thanks for the comments.. I also had a look in to the same when i prepared the work book. Sorry to say this is not possible without VBA. I was trying to explain this without the help of VBA. I will be soon coming with a post to give a solution using VBA.

      Reply
  3. Malis says:
    August 12, 2011 at 5:46 am

    Do you have the example in excel?

    Reply
  4. Dave says:
    November 10, 2011 at 9:56 am

    Hi, Further to Will’s comment, I have used the described methods to populate a hierarchy of 4 lists, each dependant on the previous one. I want users choices to be limited by the previous selection, but if, after selecting an entry for each list, a user changes the first list, an invalid combination is saved. The entries are not revalidated after editing. Any ideas would be appreciated. Thanks Dave

    Reply

Do you have any comments on Data Validation – Creating Dependent drop down list in Excel ?