One play to try ..
Assume the invoice #s are in Sheet1, in A1 down
1
1
1
3
3
2
etc
Put in C1:
=IF(A1="","",IF(COUNTIF($A$1:A1,A1)1,"",A1+ROW()/10^10))
Put in B1:
=IF(ISERROR(SMALL(C:C,ROWS($A$1:A1))),"",INDEX(A:A ,MATCH(SMALL(C:C,ROWS($A$1
:A1)),C:C,0)))
Select B1:C1, copy down to say, C100
to cover the max expected data in col A
Col B will return an ascending sort of
the unique invoice #s in col A
Now to create a dynamic range for use in the DV
Click Insert Name Define
Put in "Names in workbook": Invoice
Put in "Refers to":
=OFFSET(Sheet1!$B$1,,,SUMPRODUCT(--(Sheet1!$B$1:$B$100<"")))
Click OK
Try out the DV ..
In another / new sheet
Select the DV range
Click Data Validation
Make the settings as:
Under "Allow": List
Source: =Invoice
Click OK
The DV droplists will show the ascending sort
of the unique invoice #s in col A in Sheet1
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"Jennifer" wrote in message
...
Hey all,
My database has duplicate invoice entries. This is good. Problem arrises
on
another worksheet where I want to use these invoice numbers in a
validation
drop box but I don't want to see
1
1
1
3
3
2
2
I would like to only see one of each invoice entry in the validation.
Anyone
have any ideas. Thank you, Jennifer
--
Though daily learning, I LOVE EXCEL!
Jennifer
|