Multiple Dual-Validations

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 main that used in column B in that row.

Creating something like this basically falls in three parts

Part1: is the Names you need to define, so after we create the table found “Data” sheet …..

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

=OFFSET(Data!$A$2,0,1,1,COUNTA(Data!$2:$2))

This name will refer to the top header (which is the first validation in each of the sets)

Part2: is the Validation in column B, that list of Headers in sheet “Data”

Part3: which is the tough one is the Validation of column D, to have this formula

=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)

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.

Does that make sense?

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.

let me know if it worked or if you have questions