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.
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.
OzGrid Business Applications
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
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
You'll get the workbook thru email showing the machinery.