# Archive of Mr Excel Message Board

Back to Data in Excel archive index
Back to archive home

## Dropdown box in cell (data validation) on unique list

Posted by Adam on February 16, 2001 3:06 PM
I have a list of data in a column.

For Example
1
1
1
2
2
2
3
3

I want to put data validation on a cell so I get a drop down box of the unique values in that list.

Can anyone help. Can I do this right on the worksheet or will this require coding.

Thanks,

## Re: Dropdown box in cell (data validation) on unique list

Posted by YAli AN on February 16, 2001 3:15 PM

## Re: Dropdown box in cell (data validation) on unique list

Posted by Dave Hawley on February 17, 2001 1:11 AM

If your list will be changing all the time then, Yes you will require code. But try the Data>Filters>Advanced Filter set to Unique item only.

Dave

## Re: Dropdown box in cell (data validation) on unique list

Posted by Aladin Akyurek on February 17, 2001 5:34 AM

Dave said below in this thread that:

"If your list will be changing all the time then, Yes you will require code. But try the Data>Filters>Advanced Filter set to Unique item only."

You might consider a formula-based solution wrt a list that will be changing all the time. If you don't mind messing with relatively complex formulas, that is.

I'll assume the list of numbers with duplicates occupies the range A1:A8.

Step 1. Name the list of numbers RawSourceList via the Name Box.

Step 2. Array-enter (hit control+shift+enter to enter) the following formula
B1 =SUM(IF(A1>RawSourceList,1),1) [ copy down as far as needed ]

Step 3. Name the range B1:B8 LocList via the Name Box.

Step 4. Enter
C1 =IF(ISNA(MATCH(ROW()-ROW(\$B\$1)+1,LocList,0)),0,INDEX(RawSourceList,MATCH(ROW()-ROW(\$B\$1)+1,LocList,0))) [ copy down as far as needed ]

Step 5. Name the range C1:C8 SortList via the Name Box.

Step 6. Name the range D1:D8 NoDupsList via the Name Box and array-enter
D1 =IF(ROW()-ROW(NoDupsList)+1>ROWS(SortList)-COUNTIF(SortList,0),"",INDIRECT(ADDRESS(SMALL((IF(SortList<>0,ROW(SortList),ROW()+ROWS(SortList))),ROW()-ROW(NoDupsList)+1),COLUMN(SortList),4))) [ copy down to D2:D8 ]

Step 7. Enter
E1 ="\$D\$1:\$D\$"&COUNT(NoDupsList)

Step 8. Name E1, e.g., SelectionList

Step 9. Go to the cell where you want to have your dropdown-list. Activate Data Validation, choose the option List for Allow on Settings, and type

=INDIRECT(SelectionList)

as Source.

## Re: Dropdown box in cell (data validation) on unique list

Posted by adam on February 20, 2001 10:33 AM
I tried this and this didn't work any more help please ??

## Re: Dropdown box in cell (data validation) on unique list

Posted by Aladin Akyurek on February 20, 2001 11:19 AM

You'll get the workbook thru email showing the machinery.