{"id":203,"date":"2012-02-07T03:25:22","date_gmt":"2012-02-07T09:25:22","guid":{"rendered":"http:\/\/xlfxs.com\/?p=203"},"modified":"2012-02-07T03:25:22","modified_gmt":"2012-02-07T09:25:22","slug":"validation-based-on-another-validation","status":"publish","type":"post","link":"https:\/\/xlfxs.com\/?p=203","title":{"rendered":"Validation based on another Validation"},"content":{"rendered":"<p><span style=\"font-size: small;\">Creating a Data Validation based on another Data Validation<\/span><\/p>\n<p>Meaning that when you select from a drop down from the first the second will be filled with the list that is corresponding to it.<\/p>\n<p>You need to create two names (Formula &gt; Name Manager), one has this formula<\/p>\n<pre>=OFFSET(Sheet1!$A$2,0,1,1,COUNTA(Sheet1!$2:$2))<\/pre>\n<p>The other one has this<\/p>\n<p><!--more--><\/p>\n<pre>=OFFSET(Sheet1!$A$2,1,MATCH(Sheet2!$B$3,Cat_S,0),COUNTA(OFFSET(Sheet1!$A$2,0,MATCH(Sheet2!$B$3,Cat_S,0),50000,1))-1,1)<\/pre>\n<p>The create Data Validation to &#8220;List&#8221; first name on a cell, and another Data Validation to list the other one<\/p>\n<p>&nbsp;<\/p>\n<p><a href=\"http:\/\/xlfxs.com\/wp-content\/uploads\/2012\/02\/Validation_Validation.zip\"><img decoding=\"async\" class=\"aligncenter\" src=\"\/wp-content\/uploads\/2014\/12\/Download1.png\" alt=\"\" \/><\/a><\/p>\n\n","protected":false},"excerpt":{"rendered":"<p>Creating a Data Validation based on another Data Validation Meaning that when you select from a drop down from the first the second will be filled with the list that is corresponding to it. You need to create two names (Formula &gt; Name Manager), one has this formula =OFFSET(Sheet1!$A$2,0,1,1,COUNTA(Sheet1!$2:$2)) The other one has this<\/p>\n","protected":false},"author":1,"featured_media":204,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[75,22,3,36,37,47,6],"tags":[],"class_list":["post-203","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-counta","category-validation","category-formulas","category-lookup_and_references","category-match","category-offset","category-standard_functions"],"_links":{"self":[{"href":"https:\/\/xlfxs.com\/index.php?rest_route=\/wp\/v2\/posts\/203","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/xlfxs.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/xlfxs.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/xlfxs.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/xlfxs.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=203"}],"version-history":[{"count":0,"href":"https:\/\/xlfxs.com\/index.php?rest_route=\/wp\/v2\/posts\/203\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/xlfxs.com\/index.php?rest_route=\/wp\/v2\/media\/204"}],"wp:attachment":[{"href":"https:\/\/xlfxs.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=203"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/xlfxs.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=203"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/xlfxs.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=203"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}