Dependent drop down lists for groups of items

Shadkng

Board Regular
Joined
Oct 11, 2018
Messages
210
I would like to modify my existing drop down lists to dependent ones. I have followed some of the online tutorials and made some of my own samples to learn how do make a basic dependent one. I would like to know if something is possible and below is a small sample of what I am working with. The fabrics are one drop down list and the colors are in another drop down list. I know I can assign each of the fabrics it's own colors, but is there a way to assign a group of fabrics to a range of colors? So if I choose any of the "matte" items then the colors in the 2nd drop down would be the same. Same for any the "A3000" which would show a different set of colors. Otherwise it seems like I will have many named ranges because I have over 100 different fabrics and many colors. I just got started with these dependent lists so maybe I'm missing something obvious. Thanks


Fabrics
Matte
Matte30
Mattecol
mattesqyd
matte77
A3000 1%
A3000 3%
A3000 5%
A3000 10%

colors (these colors would be for the "matte items"
White
oyster
ivory
green

colors (these colors would be for the "A3000" items"
grey blue
char char
white linen
bronze
 

Some videos you may like

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

Col Delane

Active Member
Joined
Jan 14, 2014
Messages
303
I don't think you can get around the method of having one Named Range for each of your 1st drop-down options - but remember that you can quickly create the names from the field headings (i.e. the 1st dropdown options) using the Create from Selection option.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
38,763
Office Version
365
Platform
Windows
If you can clearly define the fabrics, then it can be done.
So if your fabrics will be 5 characters (as you've shown), or if there is always a space after the fabric name, or some other method of defining the fabric.
 

Shadkng

Board Regular
Joined
Oct 11, 2018
Messages
210
Not sure if I can define...silly question - can I define in an adjacent column somehow?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
38,763
Office Version
365
Platform
Windows
Not that I'm aware of.
With what you showed in the OP, you would have a named range called "matte_" and one called "A3000_" you could then use this in the DV
=INDIRECT(LEFT(L2,5)&"_")
where L2 is the Fabrics DV
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,095,751
Messages
5,446,284
Members
405,394
Latest member
WStockel

This Week's Hot Topics

Top