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

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