Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Validated list - how to make conditional | Excel Discussion (Misc queries) | |||
Creating a list from validated data | Excel Discussion (Misc queries) | |||
Max value in validated list | Excel Discussion (Misc queries) | |||
Unique Dynamic Validated List | Excel Worksheet Functions | |||
To make sure only the Validated list can be put in the drop down b | Excel Programming |