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

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 ..
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----
"Tan" <Tan @discussions.microsoft.com> wrote in message
news:[email protected]...
> 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.....