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

 

[Debra Dalgleish]

Without using programming, you can right-click on a cell, and select
Pick From List, and select a name from the dropdown list.

Or, using the keyboard, press Alt + the down arrow.

 

[Leith Ross]

Hello Jon,

Use the CountIf worksheet function. If the result is 0 then add th
name.

Example:
N = Excel.WorksheetFunction.CountIf(Range("A1:A50"), "Jon")

N will equal the number of times the name Jon is found in the range o
A1 to A50.

Sincerely,
Leith Ros