I have a rather large range that I am using to populate a validation list.
There are several duplicate entries. Is there some formula I can use that
will eliminate the duplicates in the drop-down menu on the validated cells?

Using Excel 2003

Thanks in advance!

Select the range of cells that are used for the validation list and:

Data Filter Advance Filter and specify unique records only

Then use the new list for validation.

--
Gary''s Student - gsnu200840


"John" wrote:

I have a rather large range that I am using to populate a validation list.
There are several duplicate entries. Is there some formula I can use that
will eliminate the duplicates in the drop-down menu on the validated cells?

Using Excel 2003

Thanks in advance!

Perhaps I'm not being specific enough. I am creating a form with pull down
menus that will populate off of another sheet. Column A is "Country", Column
B "State", Column C is "City", etc

Now I have several hundered entries. So when I set the Validation list to
Column A I get a hundered "USA" and "Canada". Is there a way that when the
pull down populates it only has USA/Canada. I can't sort the list because I
need Column B to stay unfiltered because then this needs to populate the next
pulldown.

Create a new sheet (you can hide the sheet later).

Put this in column A:
USA
Canada

In column B, put the sat names/abbreviations for the USA.

In column C, put the province names/abbreviations for Canada.

Then use name those ranges nicely and you'll be able to use the technique at
Debra Dalgleish's site:
http://contextures.com/xlDataVal02.html

John wrote:

Perhaps I'm not being specific enough. I am creating a form with pull down
menus that will populate off of another sheet. Column A is "Country", Column
B "State", Column C is "City", etc

Now I have several hundered entries. So when I set the Validation list to
Column A I get a hundered "USA" and "Canada". Is there a way that when the
pull down populates it only has USA/Canada. I can't sort the list because I
need Column B to stay unfiltered because then this needs to populate the next
pulldown.

--

Dave Peterson