Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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
|
|||
|
|||
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 "Jon C" wrote: 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 .. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Creating a dynamic list | Excel Worksheet Functions | |||
unique occurences in list | Excel Worksheet Functions | |||
unique occurences in list | Excel Worksheet Functions | |||
Pull unique names for drop down list | Excel Discussion (Misc queries) | |||
Creating a dynamic list | Excel Worksheet Functions |