"Rasheed Ahmed" wrote:
I have a list of names containing duplicate names and empty cells.
Using Data Validation List
I want get a drop down list of unique entries having no empty cells.

Another option to play with could go something like this ..

Assuming names are listed in sheet: X,
from A2 down to a max expected A2000 (say)

Put in B2:
=IF(A2="","",IF(COUNTIF($A$2:A2,A2)1,"",ROW()))

Put in C2:
=IF(ROW(A1)COUNT(B:B),"",INDEX(A:A,MATCH(SMALL(B: B,ROW(A1)),B:B,0)))

Select B2:C2, copy down to C2000 to cover the max extent in col A
(Leave B1:C1 empty)

Then click Insert Name Define and input:
Names in workbook: Names
Refers to:
=OFFSET(X!$C$2,,,SUMPRODUCT(--(X!$C$2:$C$2000<"")))
Click OK

We can now create DVs in any sheet via Data Validation, Allow: List,
Source: =Names, and the DVs will yield the required results, ie dropdowns of
only the unique names from col A in X
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---