dependant data validation

[ztg5003]

I have a data validation in cell B4. I need cell B21 to auto populate with a drop down list based on the value selected in the B4 drop down.

For Example: If BREAKFAST FOOD is selected in cell B4, I need to the list in cells F37:F40 to appear in cell B21 as a drop down list.

I know how to do this with a nested if statement for up to 7 lists but I need to do it for 17 lists.

Got any ideas? Your help is greatly appreciated.

[JBeaucaire]

If you make the entries in the P_G_Category named range have the exact same name as the other named ranges, this gets easy. For instance, correct the FABRIC_CARE. named range to remove the period at the end so it matches the option exactly from the B4 drop down exactly.

Then, in B21, use these Data Validation settings:

Allow: List
Source: =INDIRECT(B4)

[ztg5003]

That would work except I have to do this for all these attributes:

FLAVOR/SCENT:
DIETARY:
BAR SIZE:
PACKTYPE:
MULTIPACK:
UNIT SIZE:
SEASONALITY:
LIFE STAGE:
TYPE:

They all have the same categories so the list names would duplicate

[JBeaucaire]

INDIRECT() allows you to piece together strings to create a final string that is then usable as a "range" reference.

If you had a main selection cell (B4) where an option FABRIC_CARE could be chosen, then you wanted all the other categories you listed above to have special lists each based on that choice, then you can create named ranges for those like:

FLAVOR_FABRIC_CARE
DIET_FABRIC_CARE
BAR_FABRIC CARE
etc...

Then for the Flavor cell in B19, the DV formula would be this to create the correct named range based on the B4 choice:

=INDIRECT("FLAVOR_" & B4)

In B20, it would be:

=INDIRECT("DIET_" & B4)

...etc.

This should allow a virtually limitless connection to named ranges as long as they are named consistently.

BTW, remember that you can give the same cells multiple name ranges if you want to reuse one range in multiple categories.