{"id":5970,"date":"2016-07-16T09:08:36","date_gmt":"2016-07-16T09:08:36","guid":{"rendered":"http:\/\/test.nurturetechacademy.in\/?p=5970"},"modified":"2016-08-01T10:11:44","modified_gmt":"2016-08-01T10:11:44","slug":"how-to-create-a-dependent-drop-down-list","status":"publish","type":"post","link":"https:\/\/www.nurturetechacademy.in\/dev.nurturetechacademy.in\/how-to-create-a-dependent-drop-down-list\/","title":{"rendered":"How to Create a Dependent drop down list"},"content":{"rendered":"

As most of us already use drop down lists through data validation but sometimes it makes much more sense to create a dependent drop down lists e.g. you create a drop down list in cell A1 and as you choose any item from that list, the items of the other drop down list in cell B1 will change accordingly. So let\u2019s see guys how we can create it in Excel:<\/p>\n

Just have a look at the below screenshot, in this example we have a table with 4 columns with 4 States of India in each column and below each column we have respective Districts. Our motive is to create a drop down list in cell A3, which shows all the 4 States and then we will create a drop down list in cell B3 which will be dependent on the States\u2019 drop down list and will show only the respective Districts.<\/p>\n

 <\/p>\n

\"1\"<\/p>\n

 <\/p>\n

Now let\u2019s just follow the below steps to create a dependent drop down list:<\/p>\n

Step 1<\/u>\u00a0 <\/strong>First we need to create assign names to the categories row as well as all the 4 States. To do this, first select the range D1:G1 and then click in the Name box and write a name as State <\/strong>\u00a0and then hit enter.<\/p>\n

 <\/p>\n

\"2\"<\/p>\n

 <\/p>\n

Step 2<\/u><\/strong>\u00a0 Now we need to select all the respective Districts below the State one by one and give it a respective State name range through the Name box. You can just repeat the same procedure discussed in the above step to assign a name. E.g. to assign a name to the Districts of Rajasthan we will select range from D2:D24 and write Rajasthan in Name box.<\/p>\n

 <\/p>\n

\"3\"<\/p>\n

 <\/p>\n

\"4\"<\/p>\n

 <\/p>\n

\"5\"<\/p>\n

 <\/p>\n

\"6\"<\/p>\n

 <\/p>\n

Step 3<\/u><\/strong>\u00a0 Now we will create the First drop down list for States in cell A3. For this just activate cell A3 and then go to Data tab -> Data Validation<\/p>\n

 <\/p>\n

\"7\"<\/p>\n

 <\/p>\n

Now in the Data Validation dialog box, click Settings tab, choose List from the Allow drop down list, and input this formula \u00a0=State<\/strong> into the Source box and then press ok. Now we have the drop down list with 4 states in cell A3.<\/p>\n

 <\/p>\n

\"8\"<\/p>\n

 <\/p>\n

Now to create a dependent drop down list in cell B3, just activate cell B3 and then again go to Data tab \u00e0 Data Validation and now in the Data Validation dialog box, click Settings tab, choose List from the Allow drop down list, and input this formula \u00a0=Indirect(A3) <\/strong>\u00a0into the Source box and then press ok. Here we will get the list of respective Districts,\u00a0 depends on which State we choose in cell A3.<\/p>\n

 <\/p>\n

\"9\"<\/p>\n

 <\/p>\n

Note: if you have not choose anything in cell A3 then after hitting OK in the above step you will get an error like this :<\/p>\n

 <\/p>\n

\"10\"<\/p>\n

 <\/p>\n

This is just an informative error so just press OK to continue.<\/p>\n

 <\/p>\n

\"11\"<\/p>\n

 <\/p>\n

Now we are ready to use dependent drop down list \ud83d\ude42<\/p>\n

 <\/p>\n

You can download the Excel file from here<\/a>…<\/p>\n

 <\/p>\n

 <\/p>\n

 <\/p>\n

 <\/p>\n

<\/div>\n

<\/path><\/svg><\/i> \"Loading\"<\/p>\n

<\/div>\n","protected":false},"excerpt":{"rendered":"

As most of us already use drop down lists through data validation but sometimes it makes much more sense to create a dependent drop down lists e.g. you create a drop down list in cell A1 and as you choose <\/p>\n

<\/div>\n

<\/path><\/svg><\/i> \"Loading\"<\/p>\n

<\/div>\n","protected":false},"author":1,"featured_media":6074,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_bbp_topic_count":0,"_bbp_reply_count":0,"_bbp_total_topic_count":0,"_bbp_total_reply_count":0,"_bbp_voice_count":0,"_bbp_anonymous_reply_count":0,"_bbp_topic_count_hidden":0,"_bbp_reply_count_hidden":0,"_bbp_forum_subforum_count":0,"footnotes":""},"categories":[56],"tags":[71,62,73,57],"class_list":["post-5970","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-excel","tag-advanced-excel","tag-data-validation","tag-drop-down-list","tag-excel"],"_links":{"self":[{"href":"https:\/\/www.nurturetechacademy.in\/dev.nurturetechacademy.in\/wp-json\/wp\/v2\/posts\/5970","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.nurturetechacademy.in\/dev.nurturetechacademy.in\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.nurturetechacademy.in\/dev.nurturetechacademy.in\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.nurturetechacademy.in\/dev.nurturetechacademy.in\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.nurturetechacademy.in\/dev.nurturetechacademy.in\/wp-json\/wp\/v2\/comments?post=5970"}],"version-history":[{"count":3,"href":"https:\/\/www.nurturetechacademy.in\/dev.nurturetechacademy.in\/wp-json\/wp\/v2\/posts\/5970\/revisions"}],"predecessor-version":[{"id":6041,"href":"https:\/\/www.nurturetechacademy.in\/dev.nurturetechacademy.in\/wp-json\/wp\/v2\/posts\/5970\/revisions\/6041"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.nurturetechacademy.in\/dev.nurturetechacademy.in\/wp-json\/wp\/v2\/media\/6074"}],"wp:attachment":[{"href":"https:\/\/www.nurturetechacademy.in\/dev.nurturetechacademy.in\/wp-json\/wp\/v2\/media?parent=5970"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.nurturetechacademy.in\/dev.nurturetechacademy.in\/wp-json\/wp\/v2\/categories?post=5970"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.nurturetechacademy.in\/dev.nurturetechacademy.in\/wp-json\/wp\/v2\/tags?post=5970"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}