{"id":3485,"date":"2021-01-19T17:20:33","date_gmt":"2021-01-19T16:20:33","guid":{"rendered":"https:\/\/www.myexcelonline.com\/?p=3485"},"modified":"2024-02-06T16:00:42","modified_gmt":"2024-02-06T15:00:42","slug":"indirect-function-for-dependent-dropdown-lists-in-excel","status":"publish","type":"post","link":"https:\/\/www.myexcelonline.com\/meo-staging\/blog\/indirect-function-for-dependent-dropdown-lists-in-excel\/","title":{"rendered":"INDIRECT Function for Dependent Dropdown Lists in Excel"},"content":{"rendered":"<p>The INDIRECT function\u00a0is really cool as it opens up a lot of interesting combinations <a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/category\/formulas\/text\/proper\/\" target=\"_blank\" rel=\"noopener\">in Excel<\/a>.<\/p>\n<h4>We will use the power of the INDIRECT function right now on creating a Conditional Drop Down list Excel<strong>.<\/strong><\/h4>\n\n<p>Watch the data validation indirect in Excel on YouTube and give it a thumbs-up!<br \/>\n<iframe src=\"https:\/\/www.youtube.com\/embed\/kZ-m4-2rPRk?rel=0 \" width=\"800\" height=\"400\" frameborder=\"0\" allowfullscreen=\"allowfullscreen\"><span data-mce-type=\"bookmark\" style=\"width: 0px;overflow: hidden;line-height: 0\" class=\"mce_SELRES_start\">\ufeff<\/span><\/iframe><\/p>\n<p><a href=\"https:\/\/www.youtube.com\/c\/MyExcelOnlinecomtutorials?sub_confirmation=1\" target=\"_blank\" rel=\"noopener noreferrer nofollow\"><img loading=\"lazy\" decoding=\"async\" class=\"alignmiddle wp-image-111468 size-full\" style=\"vertical-align: middle\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2019\/04\/1.yt_.png\" alt=\"\" title=\"\"><\/a><\/p>\n<div class=\"after-post-box\"><a class=\"IRPP_minimalist icon-download button small\" href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/06\/Indirect-Dependent-Dropdown-List.xlsx\" target=\"_blank\" rel=\"noopener noreferrer\" data-leadbox=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/06\/Indirect-Dependent-Dropdown-List.xlsx\" data-leadbox-id=\"147284833f72a2:12f08263ab46dc\"><span class=\"ctaText px-3 text-capitalize\"><strong> download excel workbook<\/strong> <i class=\"fa fa-download\"><\/i><\/span><span class=\"postTitle px-3\">Indirect-Dependent-Dropdown-List.xlsx<\/span><\/a><\/div>\n<p>Let us go through the steps on the <a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/blog\/dependent-cascading-drop-down-lists\/\" target=\"_blank\" rel=\"noopener\">dependent drop down list Excel<\/a> in detail:<\/p>\n<p>&nbsp;<\/p>\n<p><span style=\"color: #ff0000\"><strong>STEP 1<\/strong>:<\/span>\u00a0We have our data ordered in the following columns: <strong>Category, Meat, Beverage, Breakfast.\u00a0<\/strong><\/p>\n<p>Notice that the\u00a0<strong>Category\u00a0<\/strong>column has the values\u00a0<strong>Meat, Beverage, and Breakfast<\/strong>. You will see why in our example below.<\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/06\/Indirect-Dependent-Dropdown-List-01.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" title=\"INDIRECT Function for Dependent Dropdown Lists in Excel\" class=\"alignnone size-full wp-image-3488\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/06\/Indirect-Dependent-Dropdown-List-01.png\" alt=\"INDIRECT Function for Dependent Dropdown Lists in Excel\" width=\"433\" height=\"128\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/06\/Indirect-Dependent-Dropdown-List-01.png 433w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/06\/Indirect-Dependent-Dropdown-List-01-300x89.png 300w\" sizes=\"(max-width: 433px) 100vw, 433px\" \/><\/a><\/p>\n<p>&nbsp;<\/p>\n<p><span style=\"color: #ff0000\"><strong>STEP 2<\/strong>:<\/span>\u00a0We are going to assign\u00a0<strong>Named Ranges\u00a0<\/strong>for all four columns.<\/p>\n<p>The\u00a0<strong>Name Box\u00a0<\/strong>is on the far left of the<strong> <a class=\"wpil_keyword_link\" href=\"https:\/\/www.myexcelonline.com\/meo-staging\/blog\/top-excel-formulas-function-examples-to-get-better-at-microsoft-excel\/\" target=\"_blank\" rel=\"noopener\" title=\"Formula\" data-wpil-keyword-link=\"linked\">Formula<\/a> Bar.<\/strong><\/p>\n<p><strong><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/06\/Indirect-Dependent-Dropdown-List-02.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" title=\"INDIRECT Function for Dependent Dropdown Lists in Excel\" class=\"alignnone size-full wp-image-3489\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/06\/Indirect-Dependent-Dropdown-List-02.png\" alt=\"INDIRECT Function for Dependent Dropdown Lists in Excel\" width=\"380\" height=\"146\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/06\/Indirect-Dependent-Dropdown-List-02.png 380w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/06\/Indirect-Dependent-Dropdown-List-02-300x115.png 300w\" sizes=\"(max-width: 380px) 100vw, 380px\" \/><\/a><\/strong><\/p>\n<p>&nbsp;<\/p>\n<p>Highlight the\u00a0<strong>Category\u00a0<\/strong>values, and type in the\u00a0<strong>Name Box<\/strong>\u00a0the name <em>Category<\/em><\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/06\/Indirect-Dependent-Dropdown-List-03.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" title=\"INDIRECT Function for Dependent Dropdown Lists in Excel\" class=\"alignnone size-full wp-image-3490\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/06\/Indirect-Dependent-Dropdown-List-03.png\" alt=\"INDIRECT Function for Dependent Dropdown Lists in Excel\" width=\"467\" height=\"259\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/06\/Indirect-Dependent-Dropdown-List-03.png 467w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/06\/Indirect-Dependent-Dropdown-List-03-300x166.png 300w\" sizes=\"(max-width: 467px) 100vw, 467px\" \/><\/a><\/p>\n<p>&nbsp;<\/p>\n<p><span style=\"color: #ff0000\"><strong>STEP 3<\/strong>:<\/span>\u00a0Do the same for the other three columns:<\/p>\n<p><em><strong>Meat column values &#8211; Meat (Named Range)<\/strong><\/em><\/p>\n<p><em><strong>Beverage\u00a0column values &#8211; Beverage\u00a0(Named Range)<\/strong><\/em><\/p>\n<p><em><strong>Breakfast\u00a0column values &#8211; Breakfast\u00a0(Named Range)<\/strong><\/em><\/p>\n<p>After you created all these <strong>Named Ranges<\/strong>, click on the <strong>Name\u00a0<\/strong><strong>Box dropdown<\/strong> and see our newly created\u00a0<strong>Named Ranges:<\/strong><\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/06\/Indirect-Dependent-Dropdown-List-04.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" title=\"INDIRECT Function for Dependent Dropdown Lists in Excel\" class=\"alignnone size-full wp-image-3491\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/06\/Indirect-Dependent-Dropdown-List-04.png\" alt=\"INDIRECT Function for Dependent Dropdown Lists in Excel\" width=\"267\" height=\"112\" \/><\/a><\/p>\n<p>&nbsp;<\/p>\n<p><span style=\"color: #ff0000\"><strong>STEP 4<\/strong>:<\/span>\u00a0Let us start creating the dropdown lists, select\u00a0the cell you want to place the first\u00a0<strong>dropdown list.<\/strong><\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/06\/Indirect-Dependent-Dropdown-List-05.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" title=\"INDIRECT Function for Dependent Dropdown Lists in Excel\" class=\"alignnone size-full wp-image-3492\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/06\/Indirect-Dependent-Dropdown-List-05.png\" alt=\"INDIRECT Function for Dependent Dropdown Lists in Excel\" width=\"200\" height=\"85\" \/><\/a><\/p>\n<p>Go to\u00a0<strong><em>Data &gt;\u00a0Data\u00a0<\/em><\/strong><b><i>Validation<\/i><\/b><\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/06\/Indirect-Dependent-Dropdown-List-06.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" title=\"INDIRECT Function for Dependent Dropdown Lists in Excel\" class=\"alignnone size-full wp-image-3493\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/06\/Indirect-Dependent-Dropdown-List-06.png\" alt=\"INDIRECT Function for Dependent Dropdown Lists in Excel\" width=\"643\" height=\"124\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/06\/Indirect-Dependent-Dropdown-List-06.png 643w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/06\/Indirect-Dependent-Dropdown-List-06-300x58.png 300w\" sizes=\"(max-width: 643px) 100vw, 643px\" \/><\/a><\/p>\n<p>&nbsp;<\/p>\n<p><span style=\"color: #ff0000\"><strong>STEP 5<\/strong>:<\/span>\u00a0Choose\u00a0<strong>List<\/strong> in the <strong>Allow <\/strong>drop-down, and in\u00a0the\u00a0<strong>Source <\/strong>area, type in\u00a0<strong><em>=Category<\/em><\/strong><\/p>\n<p>The reason we are doing this is to use the\u00a0<strong><em>Category<\/em> <\/strong>Named Range we defined in\u00a0<strong>Step 2.<\/strong><\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/06\/Indirect-Dependent-Dropdown-List-07.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" title=\"INDIRECT Function for Dependent Dropdown Lists in Excel\" class=\"alignnone size-full wp-image-3495\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/06\/Indirect-Dependent-Dropdown-List-07.png\" alt=\"INDIRECT Function for Dependent Dropdown Lists in Excel\" width=\"394\" height=\"319\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/06\/Indirect-Dependent-Dropdown-List-07.png 394w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/06\/Indirect-Dependent-Dropdown-List-07-300x243.png 300w\" sizes=\"(max-width: 394px) 100vw, 394px\" \/><\/a><\/p>\n<p>Click\u00a0<strong>OK.\u00a0<\/strong>Try out your drop-down list:<\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/06\/Indirect-Dependent-Dropdown-List-08.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" title=\"INDIRECT Function for Dependent Dropdown Lists in Excel\" class=\"alignnone size-full wp-image-3496\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/06\/Indirect-Dependent-Dropdown-List-08.png\" alt=\"INDIRECT Function for Dependent Dropdown Lists in Excel\" width=\"226\" height=\"111\" \/><\/a><\/p>\n<p>&nbsp;<\/p>\n<p><span style=\"color: #ff0000\"><strong>STEP 6<\/strong>:<\/span>\u00a0The moment you have been waiting for, it&#8217;s time to use our\u00a0<strong>INDIRECT\u00a0<\/strong>function!<\/p>\n<p>Select the cell where you want to place the indirect data validation list.<\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/06\/Indirect-Dependent-Dropdown-List-09.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" title=\"INDIRECT Function for Dependent Dropdown Lists in Excel\" class=\"alignnone size-full wp-image-3497\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/06\/Indirect-Dependent-Dropdown-List-09.png\" alt=\"INDIRECT Function for Dependent Dropdown Lists in Excel\" width=\"198\" height=\"71\" \/><\/a><\/p>\n<p>Go to\u00a0<strong><em>Data &gt;\u00a0Data Valdiation<\/em><\/strong><\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/06\/Indirect-Dependent-Dropdown-List-06.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" title=\"INDIRECT Function for Dependent Dropdown Lists in Excel\" class=\"alignnone size-full wp-image-3493\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/06\/Indirect-Dependent-Dropdown-List-06.png\" alt=\"INDIRECT Function for Dependent Dropdown Lists in Excel\" width=\"643\" height=\"124\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/06\/Indirect-Dependent-Dropdown-List-06.png 643w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/06\/Indirect-Dependent-Dropdown-List-06-300x58.png 300w\" sizes=\"(max-width: 643px) 100vw, 643px\" \/><\/a><\/p>\n<p>&nbsp;<\/p>\n<p><span style=\"color: #ff0000\"><strong>STEP 7<\/strong>:<\/span> Choose\u00a0<strong>List<\/strong> in the <strong>Allow <\/strong>drop-down, and in\u00a0the\u00a0<strong>Source <\/strong>area, type in\u00a0<strong><em>=INDIRECT($H$10)\u00a0<\/em><\/strong><\/p>\n<p>This will return the Named Range values from the drop-down list selected in cell H10.<\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/06\/Indirect-Dependent-Dropdown-List-10.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" title=\"INDIRECT Function for Dependent Dropdown Lists in Excel\" class=\"alignnone size-full wp-image-3498\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/06\/Indirect-Dependent-Dropdown-List-10.png\" alt=\"INDIRECT Function for Dependent Dropdown Lists in Excel\" width=\"845\" height=\"351\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/06\/Indirect-Dependent-Dropdown-List-10.png 845w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/06\/Indirect-Dependent-Dropdown-List-10-300x125.png 300w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/06\/Indirect-Dependent-Dropdown-List-10-768x319.png 768w\" sizes=\"(max-width: 845px) 100vw, 845px\" \/><\/a><\/p>\n<p>Click\u00a0<strong>OK.\u00a0<\/strong>You will get this error initially, just click\u00a0<strong>Yes\u00a0<\/strong>to continue:<\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/06\/Indirect-Dependent-Dropdown-List-11.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" title=\"INDIRECT Function for Dependent Dropdown Lists in Excel\" class=\"alignnone size-full wp-image-3499\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/06\/Indirect-Dependent-Dropdown-List-11.png\" alt=\"INDIRECT Function for Dependent Dropdown Lists in Excel\" width=\"433\" height=\"115\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/06\/Indirect-Dependent-Dropdown-List-11.png 433w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/06\/Indirect-Dependent-Dropdown-List-11-300x80.png 300w\" sizes=\"(max-width: 433px) 100vw, 433px\" \/><\/a><\/p>\n<p>&nbsp;<\/p>\n<p>For example, if we pick\u00a0<em><strong>Meat\u00a0<\/strong><\/em>in the <strong>Category List<\/strong>\u00a0dropdown,\u00a0<strong>INDIRECT<\/strong> will calculate\u00a0this as the <strong>&#8220;Meat&#8221; Named Range\u00a0<\/strong>we defined earlier and return its values in the <strong>Content List<\/strong> dropdown.<\/p>\n<p>The <em><strong>Meat\u00a0<\/strong><\/em>Named Range would represent the values:<em>\u00a0<\/em><strong><em>Beef, Chicken, Pork<\/em>:<\/strong><\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/06\/Indirect-Dependent-Dropdown-List-12.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" title=\"INDIRECT Function for Dependent Dropdown Lists in Excel\" class=\"alignnone wp-image-3500 size-full\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/06\/Indirect-Dependent-Dropdown-List-12.png\" alt=\"INDIRECT Function for Dependent Dropdown Lists in Excel\" width=\"808\" height=\"214\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/06\/Indirect-Dependent-Dropdown-List-12.png 808w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/06\/Indirect-Dependent-Dropdown-List-12-300x79.png 300w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/06\/Indirect-Dependent-Dropdown-List-12-768x203.png 768w\" sizes=\"(max-width: 808px) 100vw, 808px\" \/><\/a><\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n","protected":false},"excerpt":{"rendered":"<p>The INDIRECT function\u00a0is really cool as it opens up a lot of interesting combinations in Excel. We will use the power of the INDIRECT function right now on creating a Conditional Drop Down list Excel. Watch the data validation indirect in Excel on YouTube and give it a thumbs-up! \ufeff download excel workbook Indirect-Dependent-Dropdown-List.xlsx Let [&hellip;]<\/p>\n","protected":false},"author":2,"featured_media":17123,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"cos_headline_score":0,"cos_seo_score":0,"cos_headline_text":"INDIRECT Function for Dependent Dropdown Lists in Excel","cos_headline_has_been_analyzed":false,"cos_last_analyzed_headline":[],"om_disable_all_campaigns":false},"categories":[11,3,279],"tags":[43,134,79],"acf":[],"_links":{"self":[{"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/posts\/3485"}],"collection":[{"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/comments?post=3485"}],"version-history":[{"count":0,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/posts\/3485\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/media\/17123"}],"wp:attachment":[{"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/media?parent=3485"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/categories?post=3485"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/tags?post=3485"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}