Unique Dynamic Validated List
 

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

Unique Dynamic Validated List
 

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.

Jon C wrote:
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?

--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

Unique Dynamic Validated List
 

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

--
Leith Ros
-----------------------------------------------------------------------
Leith Ross's Profile: http://www.excelforum.com/member.php...fo&userid=1846
View this thread: http://www.excelforum.com/showthread.php?threadid=37808