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 <atyahoo<dotcom
----
"Tan" <Tan @discussions.microsoft.com wrote in message
...
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.....
|