Hi Mukeshji, The COUNTIF() function can be made use of in data validation for the range of cells that are supposed to avoid duplicate entries. Here's the procedure:
1. Select the range of cells that should avoid duplication. I am selecting J2:j201 for illustrative purpose 2. Press ALT+D followed by L to open Data Validation dialog box for the selected cells. 3. TAB to the Allow combo box and select Custom. 4. TAB to the Formula edit box and enter the following formula: =COUNTIF($J$2:$J$201, J2) = 1 (The spaces in the above formula are just for clarity, they may be ignored) 5. Activate the OK button. Note that this method helps you avoid duplication in the given cell range but doesn't allow other validations on them. I think there's a better method that can help us avoid duplication as well as perform other data validation - please help if someone on the list knows about it. -- Regards, Zujar... An optimist laughs to forget, whereas a pessimist forgets to laugh! -----Original Message----- From: AccessIndia [mailto:accessindia-boun...@accessindia.org.in] On Behalf Of Mukesh Sent: Monday, September 24, 2012 8:04 AM To: Access India Subject: Re: [AI] excel formula correction From: Mukesh Sent: Monday, September 24, 2012 5:56 AM To: Mukesh Subject: Re: excel formula correction Hello Friends, Sorry for trouble. Now I realized my mistake in formula and I have corrected it successfully. With your kind cooperation and guidance I have completed my database today. Now I have a problem of prevent duplicate entry only. If anyone of you know how to prevent duplicate entry rather than removing it later using remove duplicate function in excel. Waiting to get a positive guidance from you to complete my database today. Thank you. Regards Mukesh Baviskar Mobile: 9403161157 From: Mukesh Sent: Sunday, September 23, 2012 8:44 PM To: Access India Subject: excel formula correction Dear Friends, I have created a formula in excel but I found a error in which I can not identify. Therefore I request all of you to correct my mistake in the following formula. =COUNTIF(AN5:AN1000,"NSKMVIJAN",COUNTIF(AN5:AN1000,"NSKMVIFEB",COUNTIF(AN5:A N1000,"NSKMVIMAR",COUNTIF(AN5:AN1000,"NSKMVIAPR",COUNTIF(AN5:AN1000,"NSKMVIM AY",COUNTIF(AN5:AN1000,"NSKMVIJUNE",COUNTIF(AN5:AN1000,"NSKMVIJULY",COUNTIF( AN5:AN1000,"NSKMVIAUG",COUNTIF(AN5:AN1000,"NSKMVISEPT",COUNTIF(AN5:AN1000,"N SKMVIOCT",COUNTIF(AN5:AN1000,"NSKMVINOV",COUNTIF(AN5:AN1000,"NSKMVIDEC"))))) ))))))) Waiting for your correction tomorrow. Thank you. Regards Mukesh Baviskar Mobile: 9403161157 Search for old postings at: http://www.mail-archive.com/accessindia@accessindia.org.in/ To unsubscribe send a message to accessindia-requ...@accessindia.org.in with the subject unsubscribe. To change your subscription to digest mode or make any other changes, please visit the list home page at http://accessindia.org.in/mailman/listinfo/accessindia_accessindia.org.in Search for old postings at: http://www.mail-archive.com/accessindia@accessindia.org.in/ To unsubscribe send a message to accessindia-requ...@accessindia.org.in with the subject unsubscribe. To change your subscription to digest mode or make any other changes, please visit the list home page at http://accessindia.org.in/mailman/listinfo/accessindia_accessindia.org.in
