{"id":242,"date":"2012-02-09T04:28:08","date_gmt":"2012-02-09T10:28:08","guid":{"rendered":"http:\/\/xlfxs.com\/?p=242"},"modified":"2012-02-09T04:28:08","modified_gmt":"2012-02-09T10:28:08","slug":"multi-dual-validation","status":"publish","type":"post","link":"https:\/\/xlfxs.com\/?p=242","title":{"rendered":"Multiple Dual-Validations"},"content":{"rendered":"

It is to demonstrate how to do multiple validations in the same sheet, or as we want to call it<\/span><\/p>\n

Mutliple-dual-validations<\/strong><\/h2>\n

that depend on each other.<\/span><\/p>\n

Checking out sheet “Data”, you can tell that Validations on column B is for the heads (Main categories), and on column D is for the sub category of the main that used in column B in that row.<\/p>\n

Creating something like this basically falls in three parts<\/p>\n

Part1: is the Names you need to define, so after we create the table found “Data” sheet …..<\/p>\n

\"\"<\/a><\/p>\n

We need to set two names, one for the left-top cell for that table (used as reference) and another name with formula:<\/p>\n

<\/p>\n

=OFFSET(Data!$A$2,0,1,1,COUNTA(Data!$2:$2))<\/pre>\n
This name will refer to the top header (which is the first validation in each of the sets)<\/p>\n
Part2: is the Validation in column B, that list of Headers in sheet “Data”<\/p>\n
Part3: which is the tough one is the Validation of column D, to have this formula<\/p>\n
=OFFSET(First_Cell,1,MATCH(B3,Cat_MFR,0)-1,COUNTA(OFFSET(First_Cell,0,MATCH(B3,Cat_MFR,0)-1,10000,1))-1,1)<\/pre>\n
This one has to be set inside the Validation itself and not as name, because it holds related references based on that validation cell location.<\/p>\n
Does that make sense?<\/p>\n
Now you need to make sure that formula above is applied to cell D3, which is very critical to apply that validation considering the related references.<\/p>\n
 <\/p>\n
let me know if it worked or if you have questions<\/p>\n
 <\/p>\n\n
 <\/p>\n
\n
<\/i> Download spreadsheet<\/a><\/div>\n
 <\/div>\n<\/div>\n","protected":false},"excerpt":{"rendered":"
It is to demonstrate how to do multiple validations in the same sheet, or as we want to call it Mutliple-dual-validations that depend on each other. Checking out sheet “Data”, you can tell that Validations on column B is for the heads (Main categories), and on column D is for the sub category of the…<\/p>\n
Read More “Multiple Dual-Validations”<\/span> »<\/a><\/p>\n","protected":false},"author":1,"featured_media":257,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[75,22,3,36,37,44,47,6],"tags":[],"class_list":["post-242","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-counta","category-validation","category-formulas","category-lookup_and_references","category-match","category-names","category-offset","category-standard_functions"],"_links":{"self":[{"href":"https:\/\/xlfxs.com\/index.php?rest_route=\/wp\/v2\/posts\/242","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=242"}],"version-history":[{"count":0,"href":"https:\/\/xlfxs.com\/index.php?rest_route=\/wp\/v2\/posts\/242\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/xlfxs.com\/index.php?rest_route=\/wp\/v2\/media\/257"}],"wp:attachment":[{"href":"https:\/\/xlfxs.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=242"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/xlfxs.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=242"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/xlfxs.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=242"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}