{"id":2091,"date":"2022-03-24T23:23:21","date_gmt":"2022-03-24T18:23:21","guid":{"rendered":"https:\/\/www.yourrightdecision.com\/blog\/?p=2091"},"modified":"2022-04-12T12:16:27","modified_gmt":"2022-04-12T07:16:27","slug":"how-to-create-dynamic-named-range-in-excel-for-data-validation-and-dependent-dropdown-lists","status":"publish","type":"post","link":"https:\/\/www.yourrightdecision.com\/blog\/how-to-create-dynamic-named-range-in-excel-for-data-validation-and-dependent-dropdown-lists\/","title":{"rendered":"How to Create Dynamic Named Range in Excel for Data Validation and Dependent Dropdown Lists"},"content":{"rendered":"\n
\"\"<\/figure>\n\n\n\n

Microsoft Excel is usually used for calculations and adding formulas. However, the software can be used as a full-fledged data entry tool by using powerful data validation features and options. In this blog post, I will explain to you how you can combine Data Validation and Dynamic Named Ranges to ensure that valid data is entered and incorrect entries are not allowed by flagging an error.<\/p>\n\n\n\n

Suppose you work in a school. The school has the teaching staff, the operations staff, and the managerial staff. In an Excel Sheet, you want to enter details of each employee in three columns. Column A shows the Employee Type<\/strong> (Teacher, Operations, Managerial Post). Column B shows the Name<\/strong> of the employee. The Salary<\/strong> is entered in Column C.<\/p>\n\n\n\n

The requirement is as follows: Column A should only allow the entry of the values Teacher, Operations, and Managerial Post (Employee Type<\/strong>). Column B should be a dependent dropdown. and the values should change based on what is entered in Column A. So, for example, if Teacher<\/strong> is selected in Column A<\/strong>, Column B<\/strong> dropdown should show only teacher names. If Managerial Post<\/strong> is selected in Column A<\/strong>, Column B<\/strong> dropdown should show only manager names. Column C<\/strong> should allow entering only numbers in salary with no decimal places.<\/p>\n\n\n\n

Let’s create an Excel File to accomplish all these objectives. I have named the Excel File data.xlsx<\/strong>. Follow these steps. The finalized file will look similar to the following snapshots:<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n
\"\"<\/figure>\n\n\n\n
  • In Sheet1<\/strong>, Cell A1, enter Employee Type<\/strong><\/li>
  • In Cell B1, enter Name<\/strong><\/li>
  • In Cell C1, enter Salary<\/strong><\/li>
  • Now create another sheet Sheet2<\/strong><\/li>
  • In Sheet2, <\/strong>Cell A1, enter Employee Type<\/strong><\/li>
  • In Sheet2<\/strong> , from A2 to A4, enter A2 = Teacher<\/strong>, A3 = Operations<\/strong>, and A4 = Managerial Post<\/strong><\/span><\/li>
  • Place the cursor in Sheet2, <\/strong>Cell A1, and select FORMULAS -> Define Name -> Define Name…<\/strong><\/li>
  • In Refers to:<\/strong> enter the following formula: <\/li><\/ul>\n\n\n\n
    =OFFSET(Sheet2!$A$2,0,0,COUNTA(Sheet2!$A:$A)-1,1)<\/code><\/pre>\n\n\n\n
    • Click OK.  <\/strong>You have now created a range named Employee_Type<\/strong>. You could have simply entered:<\/li><\/ul>\n\n\n\n
      =Sheet2!$A$2:$A$4<\/code><\/pre>\n\n\n\n
      • but it would not have created a dynamic range. The use of the OFFSET<\/strong> formula has made the range dynamic. So, for example, if you enter another category in A5, it will automatically be included in the range Employee_Type<\/strong>.<\/li>
      • In Sheet2<\/strong>, enter B1=Teacher<\/strong>, C1=Operations<\/strong>, D1=ManagerialPost<\/strong>. Note that there is no space in ManagerialPost because a named ranged cannot include a space. Otherwise, the names match exactly with the entries in the Employee Type list.<\/li>
      • Now enter five values from B2 to B6 in Sheet2<\/strong> as follows: B2=Teacher 1<\/strong>, B3=Teacher 2<\/strong>, B4=Teacher 3<\/strong>, B5=Teacher 4<\/strong>, B6=Teacher 5<\/strong>.<\/li>
      • Now enter five values from C2 to C6 in Sheet2<\/strong> as follows: C2=Operation 1<\/strong>, C3=Operation 2<\/strong>, C4=Operation 3<\/strong>, C5=Operation 4<\/strong>, C6=Operation 5<\/strong>.<\/li>
      • Now enter five values from D2 to D6 in Sheet2<\/strong> as follows: D2=Manager 1<\/strong>, D3=Manager 2<\/strong>, D4=Manager 3<\/strong>, D5=Manager 4<\/strong>, D6=Manager 5<\/strong>.<\/li>
      • Place your cursor on Sheet2, <\/strong>Cell B1, and click FORMULAS -> Define Name -> Define Name…<\/strong><\/li>
      • In Refers to: <\/strong>enter the following and click OK<\/strong>:<\/li><\/ul>\n\n\n\n
        =Sheet2!$B$2:$B$6<\/code><\/pre>\n\n\n\n
        • Place your cursor on Sheet2, <\/strong>C1, and click FORMULAS -> Define Name -> Define Name…<\/strong><\/li>
        • In Refers to:<\/strong> enter the following and click OK:<\/strong><\/li><\/ul>\n\n\n\n
          =Sheet2!$C$2:$C$6<\/code><\/pre>\n\n\n\n
          • Place your cursor on Sheet2<\/strong>, D1, and click FORMULAS -> Define Name -> Define Name…<\/strong><\/li>
          • In Refers to: <\/strong>enter the following and click OK:<\/strong><\/li><\/ul>\n\n\n\n
            =Sheet2!$D$2:$D$6<\/code><\/pre>\n\n\n\n
            • Click FORMULAS -> Name Manager. <\/strong>It will list all the Named Ranges created by you and the output should match the following snapshot:<\/li><\/ul>\n\n\n\n
              \"\"<\/figure>\n\n\n\n
              • Go to Sheet1<\/strong><\/li>
              • In Sheet1<\/strong>, cell A2, you have to allow only valid entries for Employee Type<\/strong>. Therefore click DATA -> Data Validation -> Data Validation…<\/strong><\/li>
              • In the Validation criteria<\/strong>, under Allow, <\/strong>select List<\/strong><\/li>
              • In Source<\/strong>, enter the following, and click OK<\/strong><\/li><\/ul>\n\n\n\n
                =Employee_Type<\/code><\/pre>\n\n\n\n
                • Now click the dropdown in A2. You will see the valid values Teacher, Operations, Managerial Post. You can only select these values. Try entering anything else, and you will see the error message: ‘The value you entered is not valid’<\/em>. If you add another value in the Employee Type List\/Range in Sheet2<\/strong>, it will automatically appear in the dropdown.<\/li>
                • Now place the cursor in Sheet1<\/strong>, B2.<\/li>
                • Click DATA -> Data Validation -> Data Validation…<\/strong><\/li>
                • In Validation criteria<\/strong>, under Allow<\/strong>, select List<\/strong><\/li>
                • In Source<\/strong>, enter the following, and click OK<\/strong>. You will receive a message: ‘The Source currently evaluates to an error. Do you want to continue?<\/em>‘ Ignore this error and click Yes<\/strong><\/li><\/ul>\n\n\n\n
                  =INDIRECT(A2)<\/code><\/pre>\n\n\n\n
                  • Now in Sheet1, <\/strong>A2, select Teacher<\/strong> from the dropdown. Now check the dropdown in B2. It will only show teachers from Teacher 1<\/strong> to Teacher 5<\/strong>. Select Operations<\/strong> in A2. B2 will only show Operations Employees from Operation 1<\/strong> to Operation 5<\/strong>. Select Managerial Post<\/strong> in A2, B2 will now show nothing. It occurred because the entry ‘Managerial Post’ has a space in between and the range ManagerialPost is without space. The Range Name should map exactly to the Employee Type Entry for the INDIRECT formula to work.<\/li>
                  • To address this issue, a change in the formula will be needed. Place your cursor in Sheet1, <\/strong>B2, and click DATA -> Data Validation -> Data Validation…<\/strong><\/li>
                  • In Source, <\/strong>enter the following and click OK:<\/strong><\/li><\/ul>\n\n\n\n
                    =INDIRECT(SUBSTITUTE(A2,\" \",\"\"))<\/code><\/pre>\n\n\n\n
                    • Now the data entry system is almost complete with full data validation. If you select Teacher<\/strong> in A2, B2 will allow selecting Teacher 1<\/strong> to Teacher 5<\/strong>. When you select Operations<\/strong> in A2, B2 will allow selecting Operation 1<\/strong> to Operation 5<\/strong>. If Managerial Post<\/strong> is selected in A2, B2 will allow selecting Manager 1<\/strong> to Manager 5<\/strong>.<\/li>
                    • Now, the only validation required is in Sheet1, <\/strong>Column C.<\/li>
                    • Place the cursor in Sheet 1<\/strong>, C2. Select DATA -> Data Validation -> Data Validation…<\/strong><\/li>
                    • In Validation criteria, <\/strong>under Allow, <\/strong>select Whole number<\/strong>. Select Minimum <\/strong>(such as 1000) and Maximum <\/strong>(such as 1000000), and click OK. <\/strong>Now only whole numbers can be entered in the Salary<\/strong> column between 1000 and 1000000. Decimals and alphabets will not be allowed.<\/li>
                    • Clear all entries in Sheet1, <\/strong>A2, B2, and C2.<\/li>
                    • Select from A2 to C2.<\/li>
                    • Click Copy.<\/strong><\/li>
                    • Select from A3 to C25.<\/li>
                    • Select the down arrow of Paste<\/strong> and select Paste Special…<\/strong><\/li>
                    • Select Validation<\/strong> under Paste<\/strong> and click OK.<\/strong><\/li>
                    • You will see that the validation is now applied from A2 through C25.<\/li>
                    • In this way, you can apply Validation to as many cells as you like.<\/li><\/ul>\n\n\n\n
                      Some Points to Consider<\/h1>\n\n\n\n
                      • There are some limitations of this interface that you should consider. For example, select Teacher<\/strong> in A2 and Teacher 2<\/strong> in B2. Now select Operations<\/strong> in A2, B2 will still show Teacher 2<\/strong>, which is not correct. So, on the Worksheet Change Event, cell B2 should be cleared when there is a change in cell A2. If you want to implement it, save the file as data.xlsm<\/strong> (Macro-Enabled File). <\/li>
                      • Open data.xlsm. <\/strong><\/li>
                      • Press Alt+F11 to enter the programming interface of Microsoft Visual Basic for Applications<\/strong><\/li>
                      • In Project Explorer, <\/strong>double-click Sheet 1 (Sheet 1)<\/strong><\/li>
                      • From the first dropdown, select Worksheet<\/strong><\/li>
                      • From the second dropdown, select Change<\/strong><\/li>
                      • Now enter the following code:<\/li><\/ul>\n\n\n\n
                        Private Sub Worksheet_Change(ByVal Target As Range)\nApplication.ScreenUpdating = False\nIf Target.Column = 1 Then\n    Target.Offset(0, 1).ClearContents\nEnd If\nApplication.ScreenUpdating = True\nEnd Sub<\/code><\/pre>\n\n\n\n
                        • Select Teacher<\/strong> in A2 and Teacher 2<\/strong> in B2. Now select Operations<\/strong> in A2. Teacher 2<\/strong> will be cleared out in B2 and you can only enter from Operation 1<\/strong> to Operation 5<\/strong>.<\/li>
                        • I have implemented a dynamic range for the Master List\/Range Employee_Type<\/strong>. Therefore, you can add more values in Employee Type<\/strong> in Sheet2<\/strong> and it will automatically be shown in the dropdown in Sheet1<\/strong> because the OFFSET<\/strong> function has been used to create a dynamic range. However, for the dependent ranges Teacher<\/strong>, Operations<\/strong>, and ManagerialPost<\/strong>, I have used simple ranges such as =Sheet2!$B$2:$B$6, =Sheet2!$C$2:$C$6, and =Sheet2!$D$2:$D$6. It is because you can’t use INDIRECT<\/strong> with dynamic range names. INDIRECT<\/strong> converts a STRING to a RANGE. In the case of a dynamic name, the string is =OFFSET(\u2026\u2026., which cannot be converted to a range. Indirect is looking for something like =$A$1:$A$10.<\/li>
                        • If you want to make the dependent lists dynamic too, you can create the range addresses in a cell and reference that with INDIRECT<\/strong>. Suppose Teacher<\/strong> Range is to be made dynamic. Currently, Teacher<\/strong> Refers to =Sheet2!$B$2:$B$6<\/strong>. In Sheet2<\/strong>, cell F1, enter the following:<\/li><\/ul>\n\n\n\n
                          = \"'Sheet2'!$B$2:$B$\" & COUNTA(B:B)<\/code><\/pre>\n\n\n\n
                          • Select FORMULAS -> Name Manager<\/strong><\/li>
                          • Select the range Teacher<\/strong> and enter the following in Refers to: <\/strong>and click Close<\/strong><\/li><\/ul>\n\n\n\n
                            =Sheet2!$F$1<\/code><\/pre>\n\n\n\n
                            • Now select Sheet1<\/strong> and place the cursor in cell B2. <\/li>
                            • Select DATA -> Data Validation -> Data Validation…<\/strong><\/li>
                            • Enter the following in Source<\/strong> and click OK<\/strong><\/li><\/ul>\n\n\n\n
                              =INDIRECT(INDIRECT(SUBSTITUTE(A2,\" \",\"\")))<\/code><\/pre>\n\n\n\n
                              • Please note that if you follow this approach, then you will need to make all dependent ranges dynamic by creating the range addresses in a cell. It is because the Source formula has changed from INDIRECT(A2)<\/strong> to INDIRECT(INDIRECT(A2)).<\/strong><\/li>
                              • In Sheet2, <\/strong>enter a new entry Teacher 6<\/strong> in the Teacher<\/strong> column. <\/li>
                              • In Sheet1, <\/strong>select Teacher <\/strong>in Employee Type<\/strong>. Now click the dropdown of Column B<\/strong>. You will see that Teacher 6<\/strong> is automatically added to the dropdown list because the Teacher<\/strong> range is now also dynamic.<\/li><\/ul>\n","protected":false},"excerpt":{"rendered":"
                                Microsoft Excel is usually used for calculations and adding formulas. However, the software can be used as a full-fledged data…<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[27,9],"tags":[],"class_list":["post-2091","post","type-post","status-publish","format-standard","hentry","category-excel","category-tech"],"yoast_head":"\nHow to Create Dynamic Named Range in Excel for Data Validation and Dependent Dropdown Lists - Your Right Decision Blog<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.yourrightdecision.com\/blog\/how-to-create-dynamic-named-range-in-excel-for-data-validation-and-dependent-dropdown-lists\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"How to Create Dynamic Named Range in Excel for Data Validation and Dependent Dropdown Lists - Your Right Decision Blog\" \/>\n<meta property=\"og:description\" content=\"Microsoft Excel is usually used for calculations and adding formulas. However, the software can be used as a full-fledged data…\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.yourrightdecision.com\/blog\/how-to-create-dynamic-named-range-in-excel-for-data-validation-and-dependent-dropdown-lists\/\" \/>\n<meta property=\"og:site_name\" content=\"Your Right Decision Blog\" \/>\n<meta property=\"article:publisher\" content=\"https:\/\/www.facebook.com\/yourrightdecision\" \/>\n<meta property=\"article:published_time\" content=\"2022-03-24T18:23:21+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2022-04-12T07:16:27+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/www.yourrightdecision.com\/blog\/wp-content\/uploads\/2022\/03\/192.jpg\" \/>\n<meta name=\"author\" content=\"admin\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"admin\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"8 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/www.yourrightdecision.com\/blog\/how-to-create-dynamic-named-range-in-excel-for-data-validation-and-dependent-dropdown-lists\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/www.yourrightdecision.com\/blog\/how-to-create-dynamic-named-range-in-excel-for-data-validation-and-dependent-dropdown-lists\/\"},\"author\":{\"name\":\"admin\",\"@id\":\"https:\/\/www.yourrightdecision.com\/blog\/#\/schema\/person\/1619bc20b251247bf3089fa41c507641\"},\"headline\":\"How to Create Dynamic Named Range in Excel for Data Validation and Dependent Dropdown Lists\",\"datePublished\":\"2022-03-24T18:23:21+00:00\",\"dateModified\":\"2022-04-12T07:16:27+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/www.yourrightdecision.com\/blog\/how-to-create-dynamic-named-range-in-excel-for-data-validation-and-dependent-dropdown-lists\/\"},\"wordCount\":1474,\"commentCount\":30,\"publisher\":{\"@id\":\"https:\/\/www.yourrightdecision.com\/blog\/#organization\"},\"image\":{\"@id\":\"https:\/\/www.yourrightdecision.com\/blog\/how-to-create-dynamic-named-range-in-excel-for-data-validation-and-dependent-dropdown-lists\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/www.yourrightdecision.com\/blog\/wp-content\/uploads\/2022\/03\/192.jpg\",\"articleSection\":[\"Excel\",\"Technology\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/www.yourrightdecision.com\/blog\/how-to-create-dynamic-named-range-in-excel-for-data-validation-and-dependent-dropdown-lists\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.yourrightdecision.com\/blog\/how-to-create-dynamic-named-range-in-excel-for-data-validation-and-dependent-dropdown-lists\/\",\"url\":\"https:\/\/www.yourrightdecision.com\/blog\/how-to-create-dynamic-named-range-in-excel-for-data-validation-and-dependent-dropdown-lists\/\",\"name\":\"How to Create Dynamic Named Range in Excel for Data Validation and Dependent Dropdown Lists - Your Right Decision Blog\",\"isPartOf\":{\"@id\":\"https:\/\/www.yourrightdecision.com\/blog\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\/\/www.yourrightdecision.com\/blog\/how-to-create-dynamic-named-range-in-excel-for-data-validation-and-dependent-dropdown-lists\/#primaryimage\"},\"image\":{\"@id\":\"https:\/\/www.yourrightdecision.com\/blog\/how-to-create-dynamic-named-range-in-excel-for-data-validation-and-dependent-dropdown-lists\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/www.yourrightdecision.com\/blog\/wp-content\/uploads\/2022\/03\/192.jpg\",\"datePublished\":\"2022-03-24T18:23:21+00:00\",\"dateModified\":\"2022-04-12T07:16:27+00:00\",\"breadcrumb\":{\"@id\":\"https:\/\/www.yourrightdecision.com\/blog\/how-to-create-dynamic-named-range-in-excel-for-data-validation-and-dependent-dropdown-lists\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.yourrightdecision.com\/blog\/how-to-create-dynamic-named-range-in-excel-for-data-validation-and-dependent-dropdown-lists\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.yourrightdecision.com\/blog\/how-to-create-dynamic-named-range-in-excel-for-data-validation-and-dependent-dropdown-lists\/#primaryimage\",\"url\":\"https:\/\/www.yourrightdecision.com\/blog\/wp-content\/uploads\/2022\/03\/192.jpg\",\"contentUrl\":\"https:\/\/www.yourrightdecision.com\/blog\/wp-content\/uploads\/2022\/03\/192.jpg\",\"width\":700,\"height\":467},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.yourrightdecision.com\/blog\/how-to-create-dynamic-named-range-in-excel-for-data-validation-and-dependent-dropdown-lists\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.yourrightdecision.com\/blog\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"How to Create Dynamic Named Range in Excel for Data Validation and Dependent Dropdown Lists\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.yourrightdecision.com\/blog\/#website\",\"url\":\"https:\/\/www.yourrightdecision.com\/blog\/\",\"name\":\"Your Right Decision Blog\",\"description\":\"Your Right Decision on the Web\",\"publisher\":{\"@id\":\"https:\/\/www.yourrightdecision.com\/blog\/#organization\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.yourrightdecision.com\/blog\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Organization\",\"@id\":\"https:\/\/www.yourrightdecision.com\/blog\/#organization\",\"name\":\"Your Right Decision\",\"url\":\"https:\/\/www.yourrightdecision.com\/blog\/\",\"logo\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.yourrightdecision.com\/blog\/#\/schema\/logo\/image\/\",\"url\":\"https:\/\/www.yourrightdecision.com\/blog\/wp-content\/uploads\/2020\/11\/156.jpg\",\"contentUrl\":\"https:\/\/www.yourrightdecision.com\/blog\/wp-content\/uploads\/2020\/11\/156.jpg\",\"width\":364,\"height\":364,\"caption\":\"Your Right Decision\"},\"image\":{\"@id\":\"https:\/\/www.yourrightdecision.com\/blog\/#\/schema\/logo\/image\/\"},\"sameAs\":[\"https:\/\/www.facebook.com\/yourrightdecision\"]},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.yourrightdecision.com\/blog\/#\/schema\/person\/1619bc20b251247bf3089fa41c507641\",\"name\":\"admin\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.yourrightdecision.com\/blog\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/4ae389a5bfe7418706d8fd3157e4bd2b?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/4ae389a5bfe7418706d8fd3157e4bd2b?s=96&d=mm&r=g\",\"caption\":\"admin\"}}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"How to Create Dynamic Named Range in Excel for Data Validation and Dependent Dropdown Lists - Your Right Decision Blog","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.yourrightdecision.com\/blog\/how-to-create-dynamic-named-range-in-excel-for-data-validation-and-dependent-dropdown-lists\/","og_locale":"en_US","og_type":"article","og_title":"How to Create Dynamic Named Range in Excel for Data Validation and Dependent Dropdown Lists - Your Right Decision Blog","og_description":"Microsoft Excel is usually used for calculations and adding formulas. However, the software can be used as a full-fledged data…","og_url":"https:\/\/www.yourrightdecision.com\/blog\/how-to-create-dynamic-named-range-in-excel-for-data-validation-and-dependent-dropdown-lists\/","og_site_name":"Your Right Decision Blog","article_publisher":"https:\/\/www.facebook.com\/yourrightdecision","article_published_time":"2022-03-24T18:23:21+00:00","article_modified_time":"2022-04-12T07:16:27+00:00","og_image":[{"url":"https:\/\/www.yourrightdecision.com\/blog\/wp-content\/uploads\/2022\/03\/192.jpg","type":"","width":"","height":""}],"author":"admin","twitter_card":"summary_large_image","twitter_misc":{"Written by":"admin","Est. reading time":"8 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.yourrightdecision.com\/blog\/how-to-create-dynamic-named-range-in-excel-for-data-validation-and-dependent-dropdown-lists\/#article","isPartOf":{"@id":"https:\/\/www.yourrightdecision.com\/blog\/how-to-create-dynamic-named-range-in-excel-for-data-validation-and-dependent-dropdown-lists\/"},"author":{"name":"admin","@id":"https:\/\/www.yourrightdecision.com\/blog\/#\/schema\/person\/1619bc20b251247bf3089fa41c507641"},"headline":"How to Create Dynamic Named Range in Excel for Data Validation and Dependent Dropdown Lists","datePublished":"2022-03-24T18:23:21+00:00","dateModified":"2022-04-12T07:16:27+00:00","mainEntityOfPage":{"@id":"https:\/\/www.yourrightdecision.com\/blog\/how-to-create-dynamic-named-range-in-excel-for-data-validation-and-dependent-dropdown-lists\/"},"wordCount":1474,"commentCount":30,"publisher":{"@id":"https:\/\/www.yourrightdecision.com\/blog\/#organization"},"image":{"@id":"https:\/\/www.yourrightdecision.com\/blog\/how-to-create-dynamic-named-range-in-excel-for-data-validation-and-dependent-dropdown-lists\/#primaryimage"},"thumbnailUrl":"https:\/\/www.yourrightdecision.com\/blog\/wp-content\/uploads\/2022\/03\/192.jpg","articleSection":["Excel","Technology"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.yourrightdecision.com\/blog\/how-to-create-dynamic-named-range-in-excel-for-data-validation-and-dependent-dropdown-lists\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.yourrightdecision.com\/blog\/how-to-create-dynamic-named-range-in-excel-for-data-validation-and-dependent-dropdown-lists\/","url":"https:\/\/www.yourrightdecision.com\/blog\/how-to-create-dynamic-named-range-in-excel-for-data-validation-and-dependent-dropdown-lists\/","name":"How to Create Dynamic Named Range in Excel for Data Validation and Dependent Dropdown Lists - Your Right Decision Blog","isPartOf":{"@id":"https:\/\/www.yourrightdecision.com\/blog\/#website"},"primaryImageOfPage":{"@id":"https:\/\/www.yourrightdecision.com\/blog\/how-to-create-dynamic-named-range-in-excel-for-data-validation-and-dependent-dropdown-lists\/#primaryimage"},"image":{"@id":"https:\/\/www.yourrightdecision.com\/blog\/how-to-create-dynamic-named-range-in-excel-for-data-validation-and-dependent-dropdown-lists\/#primaryimage"},"thumbnailUrl":"https:\/\/www.yourrightdecision.com\/blog\/wp-content\/uploads\/2022\/03\/192.jpg","datePublished":"2022-03-24T18:23:21+00:00","dateModified":"2022-04-12T07:16:27+00:00","breadcrumb":{"@id":"https:\/\/www.yourrightdecision.com\/blog\/how-to-create-dynamic-named-range-in-excel-for-data-validation-and-dependent-dropdown-lists\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.yourrightdecision.com\/blog\/how-to-create-dynamic-named-range-in-excel-for-data-validation-and-dependent-dropdown-lists\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.yourrightdecision.com\/blog\/how-to-create-dynamic-named-range-in-excel-for-data-validation-and-dependent-dropdown-lists\/#primaryimage","url":"https:\/\/www.yourrightdecision.com\/blog\/wp-content\/uploads\/2022\/03\/192.jpg","contentUrl":"https:\/\/www.yourrightdecision.com\/blog\/wp-content\/uploads\/2022\/03\/192.jpg","width":700,"height":467},{"@type":"BreadcrumbList","@id":"https:\/\/www.yourrightdecision.com\/blog\/how-to-create-dynamic-named-range-in-excel-for-data-validation-and-dependent-dropdown-lists\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.yourrightdecision.com\/blog\/"},{"@type":"ListItem","position":2,"name":"How to Create Dynamic Named Range in Excel for Data Validation and Dependent Dropdown Lists"}]},{"@type":"WebSite","@id":"https:\/\/www.yourrightdecision.com\/blog\/#website","url":"https:\/\/www.yourrightdecision.com\/blog\/","name":"Your Right Decision Blog","description":"Your Right Decision on the Web","publisher":{"@id":"https:\/\/www.yourrightdecision.com\/blog\/#organization"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.yourrightdecision.com\/blog\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Organization","@id":"https:\/\/www.yourrightdecision.com\/blog\/#organization","name":"Your Right Decision","url":"https:\/\/www.yourrightdecision.com\/blog\/","logo":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.yourrightdecision.com\/blog\/#\/schema\/logo\/image\/","url":"https:\/\/www.yourrightdecision.com\/blog\/wp-content\/uploads\/2020\/11\/156.jpg","contentUrl":"https:\/\/www.yourrightdecision.com\/blog\/wp-content\/uploads\/2020\/11\/156.jpg","width":364,"height":364,"caption":"Your Right Decision"},"image":{"@id":"https:\/\/www.yourrightdecision.com\/blog\/#\/schema\/logo\/image\/"},"sameAs":["https:\/\/www.facebook.com\/yourrightdecision"]},{"@type":"Person","@id":"https:\/\/www.yourrightdecision.com\/blog\/#\/schema\/person\/1619bc20b251247bf3089fa41c507641","name":"admin","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.yourrightdecision.com\/blog\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/4ae389a5bfe7418706d8fd3157e4bd2b?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/4ae389a5bfe7418706d8fd3157e4bd2b?s=96&d=mm&r=g","caption":"admin"}}]}},"_links":{"self":[{"href":"https:\/\/www.yourrightdecision.com\/blog\/wp-json\/wp\/v2\/posts\/2091","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.yourrightdecision.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.yourrightdecision.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.yourrightdecision.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.yourrightdecision.com\/blog\/wp-json\/wp\/v2\/comments?post=2091"}],"version-history":[{"count":8,"href":"https:\/\/www.yourrightdecision.com\/blog\/wp-json\/wp\/v2\/posts\/2091\/revisions"}],"predecessor-version":[{"id":2105,"href":"https:\/\/www.yourrightdecision.com\/blog\/wp-json\/wp\/v2\/posts\/2091\/revisions\/2105"}],"wp:attachment":[{"href":"https:\/\/www.yourrightdecision.com\/blog\/wp-json\/wp\/v2\/media?parent=2091"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.yourrightdecision.com\/blog\/wp-json\/wp\/v2\/categories?post=2091"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.yourrightdecision.com\/blog\/wp-json\/wp\/v2\/tags?post=2091"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}