Unique dynamic data validation list

[Polanskiman]

Hello,

I have 2 sheets. Sheet 1 is the source data, Sheet 2 is a user sheet. I need in Sheet 2 multiple drop down lists as following:

Book1
	D	E
3	Product name	Element
4	Product 1	Mn
5	Product 2	Mn
6	Product 3	Zn
7	Product 4	Zn
8	Product 5	B
9	Product 6	Cu
10	Product 7	Cu
11	Product 8	Mo
12	Product 9	Mo
13	Product 10	Ni
14	Product 11	Co
Sheet1

Book1
	D	E
3	Product	Element
4	Dropdown list of all Mn products from sheet 1	Mn
5	Dropdown list of all Zn products from sheet 1	Zn
6	Dropdown list of all B products from sheet 1	B
7	Dropdown list of all Cu products from sheet 1	Cu
8	Dropdown list of all Mo products from sheet 1	Mo
9	Dropdown list of all Ni products from sheet 1	Ni
10	Dropdown list of all Ni products from sheet 1	Co
Sheet2

Obviously I could make very easily multiple ranges for each drop down, but I would like to have a single formula than I can use for all drop down lists. How can I achieve that?

 

[Peter_SSs]

Yes that seems seriously more involved. It does works as expected though. Thumbs up for that.

Cheers.

Why are you using full column references rather than just a specified range?

In those places that I have used them I don't think there is any performance detriment as there should be no instances where calculations will go beyond the used range anyway. However, there would be no problem in using limited ranges if you wish.