Home |
Search |
Today's Posts |
#1
|
|||
|
|||
lists and validation
Hi - I am using data validation list to create a list. I would like to in the same cells use data validation custom with countif to limit duplicates. what can I do. I do not really want to use vba if I don't have to.any suggestions and can someone point to me to the right direction andrewm -- andrewm ------------------------------------------------------------------------ andrewm's Profile: http://www.excelforum.com/member.php...o&userid=23130 View this thread: http://www.excelforum.com/showthread...hreadid=381560 |
#2
|
|||
|
|||
You can't use COUNTIF to remove duplicates, you will need to filter the
source using DataFilterAdvanced Filter with Unique Records checked and Filter in Place. -- HTH Bob Phillips "andrewm" wrote in message ... Hi - I am using data validation list to create a list. I would like to in the same cells use data validation custom with countif to limit duplicates. what can I do. I do not really want to use vba if I don't have to.any suggestions and can someone point to me to the right direction andrewm -- andrewm ------------------------------------------------------------------------ andrewm's Profile: http://www.excelforum.com/member.php...o&userid=23130 View this thread: http://www.excelforum.com/showthread...hreadid=381560 |
#3
|
|||
|
|||
Try using the vlookup and "isna" functions in the custom validation.
Something like: =isna(vlookup(a10,a$1:a9,1,false))=true. this will try to lookup the newly entered value in the cells above, and if it doesn't find it above, it will allow entry, if there is a duplicate above, it will alert Good luck -- Mike L "andrewm" wrote: Hi - I am using data validation list to create a list. I would like to in the same cells use data validation custom with countif to limit duplicates. what can I do. I do not really want to use vba if I don't have to.any suggestions and can someone point to me to the right direction andrewm -- andrewm ------------------------------------------------------------------------ andrewm's Profile: http://www.excelforum.com/member.php...o&userid=23130 View this thread: http://www.excelforum.com/showthread...hreadid=381560 |
#4
|
|||
|
|||
Another option is to hide the previously used items, as explained he
http://www.contextures.com/xlDataVal03.html andrewm wrote: Hi - I am using data validation list to create a list. I would like to in the same cells use data validation custom with countif to limit duplicates. what can I do. I do not really want to use vba if I don't have to.any suggestions and can someone point to me to the right direction -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Adding entry to validation list without retyping all lists | Excel Discussion (Misc queries) | |||
6 Data Validation lists depending on 1 cell value | Excel Discussion (Misc queries) | |||
Data Validation - Drop down lists - if then? | Excel Discussion (Misc queries) | |||
how do I add data validation dropdown lists to a Form | Excel Worksheet Functions | |||
Data Validation Lists | Links and Linking in Excel |