Data Validation in Excel

%3CLINGO-SUB%20id%3D%22lingo-sub-1357152%22%20slang%3D%22en-US%22%3EData%20Validation%20in%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1357152%22%20slang%3D%22en-US%22%3E%3CP%3EHello%20guys%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20a%20situation%20where%20i%20need%20to%20create%20some%20multiple%20independent%20data%20validation%20columns.%20The%20thing%20is%20that%20i%20need%20that%202%20separate%20columns%20to%20be%20dependent%20by%20the%20same%20column.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ETo%20be%20concrete%2C%20here%20is%20the%20situation.%20I%20have%20Channel%20as%20my%20unique%20column%20(%20Hypermarkets%2C%20Supermarkets%2C%20Drug%20etc.%20)%20and%20i%20need%20both%20the%20Client%20Column%20(%20Carrefour%2C%20Dm%2C%20Auchan%20)%20%26amp%3B%20Brand%20Column%20(%20Elseve%20%2C%20Garnier%2C%20Lancome%20)%20to%20be%20dependent%20by%20Channel%20Column.%20I%20tried%20with%20the%20same%20named%20range%20but%20i%20can't%20have%20it%20twice.%20Basically%20when%20i%20select%20Hypermarkets%2C%20to%20be%20able%20to%20see%20all%20the%20Clients%20labelled%20as%20Hypermarket%20and%20all%20the%20Brands%20found%20in%20Hypermarkets.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%2C%3C%2FP%3E%3CP%3EBr%2C%3C%2FP%3E%3CP%3EBogdan%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1357152%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1357674%22%20slang%3D%22en-US%22%3ERe%3A%20Data%20Validation%20in%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1357674%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F646409%22%20target%3D%22_blank%22%3E%40BogdanST%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAssuming%20you%20have%20two%20separate%20data%20validation%20lists%20for%20each%20Channel%20entry--i.e.%2C%20for%20Hypermarket%20you%20have%20a%20HyperClient%20AND%20a%20HyperBrand%20list%2C%20you%20can%20use%20a%20VLOOKUP%20to%20find%20the%20name%20(named%20range)%20of%20the%20lists%20for%20Hypermarket%2C%20and%20then%20INDIRECT%20in%20the%20data%20validation%20address%20to%20refer%20to%20either%20HyperClient%20or%20HyperBrand%20when%20you%20are%20in%20the%20Client%20or%20Brand%20drop%20downs...%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Occasional Visitor

Hello guys,

 

I have a situation where i need to create some multiple independent data validation columns. The thing is that i need that 2 separate columns to be dependent by the same column.

 

To be concrete, here is the situation. I have Channel as my unique column ( Hypermarkets, Supermarkets, Drug etc. ) and i need both the Client Column ( Carrefour, Dm, Auchan ) & Brand Column ( Elseve , Garnier, Lancome ) to be dependent by Channel Column. I tried with the same named range but i can't have it twice. Basically when i select Hypermarkets, to be able to see all the Clients labelled as Hypermarket and all the Brands found in Hypermarkets.

 

Thank you,

Br,

Bogdan

1 Reply
Highlighted

@BogdanST 

 

Assuming you have two separate data validation lists for each Channel entry--i.e., for Hypermarket you have a HyperClient AND a HyperBrand list, you can use a VLOOKUP to find the name (named range) of the lists for Hypermarket, and then INDIRECT in the data validation address to refer to either HyperClient or HyperBrand when you are in the Client or Brand drop downs...