Unique Dynamic Validated List

[Jon C]

Hi,

I have a column that will contain names. I'd like to use validation to make
sure that when a name is added it can be selected from a drop down assuming
the already appears in the column already. If not you can add a new name.

This works fine by defining a dynamic named range of the column as the
validation list. The problem however is that it doesn't give a unique list
i.e. the range is the whole of the column entered so far hence I get
multiple occurrences of the same name.

Does anyone know how I can make this a unique list?

TIA,

Jon C

..

 

[Guest]

Let's assume your data for now is in A1:A10. Place this in B1, press
<ctrl><shift><enter> and drag down until you see error values:

=INDEX($A$1:$A$10,SMALL(IF(ROW($A$1:$A$10)=MATCH($A$1:$A$10,$A$1:$A$10,0),ROW($A$1:$A$10)),ROW()))

You can replace A1:A10 with a dynamic range if you need to. I'd probably
create a dynamic range in column A, starting in A1, and define a name for it
(Ctrl+F3).

If your list does not start in row 1, use the following:

=INDEX(rng,SMALL(IF(ROW(rng)-N+1=MATCH(rng,rng,0),ROW(rng)-N+1),ROW()-N+1))

where N = the row number of where the list begins and "rng" is your range.

Don't worry about the error values when creating a dynamic range for
validation. You can create a dynamic range that ignores them (ie use COUNT in
your OFFSET formula).

HTH
Jason
Atlanta, GA