Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Shorten Data Validation list
Hi,
i know i've done it in the past but i can't seem to figure out what the formula is for this one. I have a list based off of a query that can list multiple deparments in the column i'm using for my validation. for example. Department. Streets N Streets N Streets N Parks E Parks E Fire Fire What i want the validation to do i show only 1 Streets N instead of 3, 1 Parks E instead of 2, and 1 Fire instead of 2 Hope that makes sense. Thanks, Ben |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Shorten Data Validation list
You have to extract the unique entries to another list and then use that new
list as the source for your drop down list. One way to do that is to use the advanced filter. Assume your data is in the range A1:A8. Select the entire range including the column header (Department) Goto the menu DataFilterAdvanced Filter If a message box pops up read it and take the appropriate action. Select: Copy to another location Copy to: enter a cell reference where you want the new list to appear, say, B1 Select: Unique records only OK Use this new list as the source for your drop down list (excluding the column header). -- Biff Microsoft Excel MVP "Ben" wrote in message ... Hi, i know i've done it in the past but i can't seem to figure out what the formula is for this one. I have a list based off of a query that can list multiple deparments in the column i'm using for my validation. for example. Department. Streets N Streets N Streets N Parks E Parks E Fire Fire What i want the validation to do i show only 1 Streets N instead of 3, 1 Parks E instead of 2, and 1 Fire instead of 2 Hope that makes sense. Thanks, Ben |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Shorten list into several columns | Excel Discussion (Misc queries) | |||
Pick List - Shorten KeyStrokes | Excel Discussion (Misc queries) | |||
data validation invalid in dynamic validation list | Excel Discussion (Misc queries) | |||
data validation invalid in dynamic validation list | Excel Worksheet Functions | |||
Macro to shorten a list | Excel Discussion (Misc queries) |