Conditional Data Validation

Able Excel Gurus,

I need your help here again.

From the attached, my source data is in columns AA - AD.
However, the inputs I want help on will be done on columns A and B.

What I want is to run a data validation on B which will be dependent on the brand selected in A. Column B should only carry the SKUs under the selected brand in A and not just all the SKUs.


Thanks in advance.

I would approach as follows:

1) Create named ranges in column AD, named with the brand such that named range "AP" is range AD2:AD6 , named range "AQ" is range AD7:AD9 etc

2) In cell B4 use data validation as List type, with the formula

Formula:

=INDIRECT(A4)

First time you type it in you may get an "Evaluates to an error" message, just ignore that.

You can lay out your named ranges however you like, I normally use one column per category.

The Indirect() function simply takes a string and tries to evaluate it to a range.

Dependent Drop Downs from Sorted List is what your looking for.
The how-to's will be too lengthy to post here, so I am recommending that you read up on the process here: www.contextures.com/xlDataVal13.html

I applied the process (mentioned at the above URL) to your sample file which I am re-attaching.