Data Validation using List (But needs unique list in drop down lis

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi all,

In sheet 1, column A is my title name while column B is person name.
Sheet 1 is my database where i do data entry in this.

In sheet 2, contains my query page. In cell A5, i uses data validation -
list, on this cell. Say in sheet 1 :

column A column B
XXXXXXX Mr A
YYYYYYYY Mr A
ZZZZZZZZ Mr A
AAAAAAA Mr B
WWWWW Mr C
DDDDDDD Mr C

But In sheet 2, cell A5, I saw in the drop down list as follows:

Mr A
Mr A
Mr A
Mr B
Mr C
Mr C


But i want to see this in cell A5 instead (Unique name that is) :

Mr A
Mr B
Mr C

Thanks a lot.....
 
One play ..

In Sheet1, in B1 down is the names list

Mr A
Mr A
Mr A
Mr B
Mr C
Mr C
etc

Put in C1:
=IF(ISERROR(SMALL(D:D,ROWS($A$1:A1))),"",INDEX(B:B,MATCH(SMALL(D:D,ROWS($A$1
:A1)),D:D)))

Put in D1:
=IF(B1="","",IF(COUNTIF($B$1:B1,B1)>1,"",ROW()))

Select C1:D1, fill down to say, D100 to cover the max expected data in col B

Create a dynamic range "MyNames"
----------
Click Insert > Name > Define

Put in "Names in workbook:" box : MyNames

Put in the "Refers to:" box:
=OFFSET(Sheet1!$C$1,,,SUMPRODUCT(--(Sheet1!$C$1:$C$100<>"")))

In Sheet2
----------
Select A5
Click Data > Validation
Under "Allow:", select: List
Source: =MyNames
Click OK

The DV droplist in A5 will show only the unique list of names, viz. for the
sample data in Sheet1, it'll appear as:

Mr A
Mr B
Mr C

Adapt to suit ..
 
Back
Top