MrExcel.com - Your One Stop for Excel Tips & Solutions

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,

Adam

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

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


Hi Adam

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

OzGrid Business Applications

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.

Aladin


Posted by adam on February 20, 2001 10:33 AM

I tried this and this didn't work any more help please ??


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


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

Aladin