Thread: Data Validation with Huge Dependency list

Hi all..


I have a workbook which has 2 sheets.. Lets call them Sheet A & Sheet B.

Sheet A is the master list of the products

Sheet B deals with daily production.

Now say i have the following in my sheet A


Product Code / Variant / Thickness / Std. Weight
AA / 1 / 20 MM / 100 gm
AA / 1 / 30 MM / 120 gm
AA / 2 / 20 MM / 110 gm
BB / 1 / 30 MM / 90 gm
BB / 3 / 30 MM / 100 gm
CC / 1 / 5 MM / 200 gm
CC / 2 / 5 MM / 150 gm
CC / 1 / 20 MM / 175 gm
CC / 2 / 10 MM 130 gm


Now in sheet B is having these columns

Date
Product Code
Variant
Thickness
Std weight
Actual Weight
qty



Now for product code, i have created a drop down using data validation.
Now i want that there should be drop down for Variant & thickness with relevant options.

I mean if product BB is selected, Variant cell should gimme option of "1" or "3" only & depending on such selection, thickness cell should gimme relevant options..

Std weight should automatically come as per what is given in Sheet A for the Selected product code with the selected attributes.



What i have found while doing research is, it is possible to have a dependent list based using indirect function & defining names to the list..

In my case this is not practical as my product list itself is about 400 & if i consider all variants then it goes well beyond 1000. Furthermore there are additions to product list & variants every month.. hence it is extremely difficult to define name..


your help will be highly appreciated.

Thanks

I can show you how to do this with only a couple of named formulas for the dependent ranges. Post up your sample workbook and I can demonstrate it for you.

You can take a stab at implementing it on your own, the technique is published here:

Sub DV Lists from a Larger DV List