Unique dynamic data validation list

Polanskiman

Board Regular
Joined
Nov 29, 2011
Messages
112
Office Version
  1. 365
  2. 2016
  3. 2011
  4. 2010
  5. 2007
Platform
  1. Windows
  2. MacOS
  3. Mobile
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
DE
3Product nameElement
4Product 1Mn
5Product 2Mn
6Product 3Zn
7Product 4Zn
8Product 5B
9Product 6Cu
10Product 7Cu
11Product 8Mo
12Product 9Mo
13Product 10Ni
14Product 11Co
Sheet1


Book1
DE
3ProductElement
4Dropdown list of all Mn products from sheet 1Mn
5Dropdown list of all Zn products from sheet 1Zn
6Dropdown list of all B products from sheet 1B
7Dropdown list of all Cu products from sheet 1Cu
8Dropdown list of all Mo products from sheet 1Mo
9Dropdown list of all Ni products from sheet 1Ni
10Dropdown list of all Ni products from sheet 1Co
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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,526
Office Version
  1. 365
Platform
  1. Windows
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.
 

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.

Watch MrExcel Video

Forum statistics

Threads
1,129,730
Messages
5,638,031
Members
417,000
Latest member
JasonWilliam

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top