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 .. |
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 .. |
All times are GMT +1. The time now is 10:33 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com