Shorten Data Validation list

[Ben]

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

 

[T. Valko]

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 Data>Filter>Advanced 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).