Multi-Column Data Validation

[lms100]

Hi,

I am looking to do multi-column data validation. In short, we have 4 columns of data: Product Type, Gender, Style, Size.

Based upon selection of data in column 1, only certain data should show in column 2. Based upon selection in column 1 and 2, only certain values are available in column 3. And based upon selection of column 1,2,3, only certain options are available in column 4. We can map out all these options. We want to make it so the drop down / list of values / data validation only allows the user to choose valid values.

I can put all the data here as it is very exhaustive. But here are some examples:

1) Product Type - Tee is selected
2) Because Tee is selected, the following genders are available: mens, womens
3) User selects mens. Because Tee, Mens is selected, the following styles are available: basic, long, short
4) User selects basic. Because user selected Tee, Mens, Basic, the following sizes are available: S,M,L

Hopefully this makes sense. Look forward to hearing back on how to set this up.

Thanks

 

[kweaver]

Do some research here or on the net re Dependent Drop Downs. Been covered numerous times.

 

[lms100]

Do some research here or on the net re Dependent Drop Downs. Been covered numerous times.

I did some research but could not find. Please send link if you found. Ideally I am looking to do this without VBA as I don't have those skills. Thanks

 

[kweaver]

Here's a fairly good explanation: Dependent

 

[lms100]

Here's a fairly good explanation: Dependent

First, thank you for your support and being responsive. I have found a similar link and been able to replicate this. The problem is when we go beyond level 2. Meaning when we get to level 3 and it is dependent on the previous 2 options. I have tried setting this up as a separate validation, but that did not work because you can't have the same named tables. So this is why I reached out and posted.

 

[kweaver]

I'll try setting up something like this and see what results. There are so many really great (aka skilled) people here, so you're likely to get some other advice or ideas to try.

BTW: it might help if you changed/updated your profile to indicate which version of Excel you run.

 

[Akuini]

@lms100
I share a macro to set up multi dependent data validation with vba. Some versions of dependent data validation use formulas to set them up, usually with indirect functions. For 2 or 3 dependent data validation & small data set, it's pretty easy to set it up, but for more than 3, it might be difficult to maintain as it requires lots of tables & lots of helper columns. This version uses vba, you only need 1 table, 1 helper column & 1 named range. The code is a bit complicated but easy to set up and maintain.
Here:

3 or More Dependent Data Validation, with VBA, easy to set up

I want to share a macro to set up multi dependent data validation with vba. Some versions of dependent data validation use formulas to set them up, usually with indirect functions. For 2 or 3 dependent data validation & small data set, it's pretty easy to set it up, but for more than 3, it might...

www.mrexcel.com