Excel Dependent Drop Down list

Original Question:-

How to create dependent drop down list in Excel?

Sheet! Data comprises list of revised data.
Sheet! Actual – Col A (I had created the dropdown list)
Sheet! Actual – Col B (I need help to create a droplist of Name of children that are dependent on the selected parent).

 

 

 
Data Sheet:-

Sheet 1

 

Actual Sheet:-

image 2

 

For dynamic drop down menu we use Excel index function along with Match function.

To create dependent drop down list in Excel follow below given steps:-

  • Define the name of range parent list and Child list.
  • Select the parent list range.
  • Go to “Formulas” tab > Name Manager > New Name > Define the name > Define the range to parent.

image 3

 

  • By following the same steps define the name to child range.

image 4

 

  • In Actual sheet select the cell A4 where we want to create the parent’s drop down list.
  • Go to Data tab > Data validation > Settings > List > Source (=Parents) > Click on ok.

image 5

 

  • To create the children’s drop down list follow below steps:-
  • Go to Data tab > Data validation > Settings > List
  • Enter the formula in Source =INDEX(Child_Names,MATCH(A4,Parents,0),0)
  • Click on ok.

image 6

 

When we select the Parent name from the list then their children name’s list will be contain.

 

Dependent Drop list in Excel

 
Below you can find more example

How to edit a drop down list in Excel?

How to delete drop down list?

How to apply Conditional Formatting in drop down list?

How to create multiple dropdown List without repetition using named ranges?

How to create drop down list?
 

If you liked our blogs, share it with your friends on Facebook. And also you can follow us on Twitter and Facebook.
We would love to hear from you, do let us know how we can improve, complement or innovate our work and make it better for you. Write us at info@exceltip.com

 
 

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Terms and Conditions of use

The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.

Visit Us On TwitterVisit Us On FacebookVisit Us On Google PlusVisit Us On Youtube