ExcelBanter - Populate combo box with unique values only

.. I want my combo box to contain unique values only.

Here's one way to set it up ..
(Combo box is assumed drawn from the forms toolbar)

In Sheet2,
Using 2 adjacent empty cols
Put in D1: =IF(C1="","",IF(COUNTIF($C$1:C1,C1)1,"",ROW()))
Put in E1:
=IF(ISERROR(SMALL(D:D,ROW())),"",
INDEX(C:C,MATCH(SMALL(D:D,ROW()),D:D,0)))
Select D1:E1, copy down to say, E100,
to cover the max expected extent of data in col C

Click Insert Name Define, and put in
Names in workbook: MyList
Refers to:
=OFFSET(Sheet2!$E$1,,,SUMPRODUCT(--(Sheet2!$E$1:$E$100<"")))

Now, put as the input range for the combo box: MyList
The combo box will display only the unique values from col C in Sheet2
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"sjayar" wrote in
message ...

Hi All,

I have a worksheet which has a combobox and i am populating with the
sheet2 values(assume column c values).

this works fine.
But........... sheet's coulmn c contains 50 values with repeated values
in it.

I want my combo box to contain unique values only.

_*example:*_
SHEET2 COLUMN C

1. john
2. Harry
3. John
4. Petter
5. Harry
6. Petter
7. petter