Selected Answer
This is a little intricate. In the attached workbook I have declared 3 pairs of named ranges as follows.
- Codes = Sheet1!A2:A1000
- Code = TXL_5251!A2:A1000
- Brands = Sheet1!B2:B1000
- Brand = TXL_5251!B2:B1000
- Origins = Sheet1!C2:C1000
- Origin = TXL_5251!C2:C1000
In each case the plural form is the range which contains duplicates and the singular form contains unique values only. All ranges contain blank cells at the bottom. These blanks are removed by using variations of the formula below in the name declaration:-
=sheet1!$A$2:INDEX(sheet1!$A$2:$A$1000,SUMPRODUCT(--(sheet1!$A$2:$A$1000<>"")))
This formula defines a range starting in Sheet1!A2 and ending with the last used cell in Sheet1!A:A. The limit is 1000 rows but that number can be increased if you need more room. You might also make the number of rows smaller, especially for the ranges on TXL_5251. The number must be the same within each formula but can be different for each range. All six formulas are of the same type, only sheet name and columns are changed.
I recommend to use a dedicated sheet for the lists. That is better than to use an "unused" part of an existing sheet. You may decide to make the sheet VeryHidden using the VBA editor or even VBA code.
So far the named ranges on the TXL_5251 tab are empty. Enter this formula in TXL_5251!A2.
=IFERROR(INDEX(Codes, MATCH(0, COUNTIF(TXL_5251!A$1:A1, Codes), 0)),"")
In Office 365 the formula will work just as I have shown it above. In Excel 2010 you may have to enter it as an array formula, using Ctl+Shift+Enter to confirm it. In the attached workbook I entered array formulas.
Make copies of this formula to B2:C2. Excel will change the referenced columns in this process. Change the two references to "Codes" to "Brands" and "Origins" in the two copies. Finally, copy the row A2:C2 down to A2:C1000 or whichever maximum number you expect to require. In the attached workbook I made copies to only row 20. If there are more values in Codes than you have formulas in Code some codes will not appear in the dropdown without warning.
This formula will create a list of unique values extraced from the ranges Codes, Brands and Origins and many blank cells at the bottom of each list. Remember that the dynamic named range removes the blanks. Therefore the named ranges on the TXL_5251 sheet actually contain only what you see.
You can now use these named ranges as list sources for your validation drop-downs. This is shown in Sheet1!D:G1 of the attached file. D1 has this formula in the validation specification, referring to the range on TXL_5251.
=Code
In the attached workbook D1 is blank. This is because your event procedure makes it so. A working copy of it is in G1. E1:F1 refer to ranges Brand and Origin.