Data Validation

[proexcel]

Hi
I have more than 10 different list with different categories
for example
I have a cosmetic shop with different Goods and colors

powder 4 colors 206-207-208-209

lipstick 8 colors 301-302-...-308

foundation 6 colors 501-501-...-506

and I have more than 20 goods

my question is how can I define name in name manger and linked it to data validation list that when I choose the powder just the powder colors appear and when I choose the lipstick just the lipstick colors appear and so on, I just want when choose each product only that product colors appear in my drop down list

 

[Oaktree]

Hi there.

The solution you're looking for is commonly referred to as "dependent lists" in Excel.

Aladin has a great write-up (one of our Hall of Fame winners!) on how to construct them here: https://www.mrexcel.com/forum/excel-questions/32-cascading-combo-boxes.html#post126

 

[proexcel]

it's not helpful because I have 20 different lists that want when I choose one of them the colors related to that product appear and when I choose another so another one appear

 

[Oaktree]

I'm not sure what you mean "it's not helpful".

Sure, Aladin's post references two countries each with 4 or 3 cities where you have 20 goods each with a different amount of colors, but the basic idea of using INDIRECT within data validation to reference a dependent named range can easily be scaled to accommodate your larger data set.

Maybe try it?

 

[proexcel]

Thanks bro I did it but let me explain more about my problem
as you give me an instruction it done but I have an invoice which has for example 20 goods in my sheet and maybe I want to add another goods, an invoice has many records and in each record you can choose different product so I want when choose Polish so different colors of polish appear in that record I choose this product, in another record I choose foundation so foundation colors appear and so on, I want to write a formula in Data validation to automatically realize which product chosen so show me the color list of the exact product, my question is possible to write a formula for dynamic list or no?