Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

show values in multiple data validation without duplicated and blanks

0

hello

I m trying  to  find  way  to shows  the  data validation in COL D,E,F are linked with COL A,B,C  without any duplicted values or blank    I  successed with COL D but doesn't  work with COL E,F  

so what  I want  showing  data validition in COL D,E,F  without any duplicated or blanks

thanks in advance 

Answer
Discuss

Answers

1
Selected Answer

This is a little intricate. In the attached workbook I have declared 3 pairs of named ranges as follows.

  1. Codes = Sheet1!A2:A1000
    1. Code = TXL_5251!A2:A1000
  2. Brands = Sheet1!B2:B1000
    1. Brand = TXL_5251!B2:B1000
  3. Origins = Sheet1!C2:C1000
    1. 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.

Discuss

Discussion

Variatus
well done !!  this  is  better  than  the  code I have  
many  thanks  for  this  a unique answering 
speed (rep: 40) Mar 16, '21 at 4:13 am
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login