Excel - Data Validation - List of unique values

Asked By Oscar on 17-Feb-10 05:58 AM
Is it possible to get list of unique values for data validation. In range a2
- a10 that I use as data validation list i have values x, y, z, x, x, ... In
a drop down I get letter x listed more than one time. Is it possible to make
it unique? I use Excel 2003.


Roger Govier replied to Oscar on 17-Feb-10 06:58 AM
Hi Oscar

Use a different source as your DV list that only has one of each entry.
If your list is as short as you say, then manually typing it would be the
easiest option.

If, in fact it is a much bigger list in reality, then use Advanced filter
Select your source range>Data>Advanced Filter>Unique values only>Copy to new
location say C2
Create a dynamic range for your DV
Name        myVal
Refers to   =$C$2:INDEX($C:$C,COUNTA($C:$C))

Roger Govier

__________ Information from ESET Smart Security, version of virus signature database 4873 (20100217) __________

The message was checked by ESET Smart Security.

Oscar replied to Roger Govier on 17-Feb-10 08:59 AM
I hoped there is a simple way to get unique values (like autofilter works).
Some WB looks the best way to take care of the list... Thanks anyway, Oscar.