+ Reply to Thread
Results 1 to 5 of 5

Function in data validation that keeps user from entering duplicate data?

  1. #1
    Registered User
    Join Date
    08-30-2007
    Posts
    28

    Function in data validation that keeps user from entering duplicate data?

    There is a function that you enter into data validation that keeps users from entering the same thing in twice in a whole column. I think it actually checks for the cells value to equal/1/True to another value that is the same and throws the error message. I lost the code I had. Anyone know of this? I pulled it from excel bible 2007 book a while ago...

    It actually is a very helpful tool for data duplication.

    Thanks.
    Branden
    *Currently Noob VBA Scripter but I know excel pretty well otherwise.

  2. #2
    Valued Forum Contributor Richard Schollar's Avatar
    Join Date
    05-23-2006
    Location
    Hampshire UK
    MS-Off Ver
    Excel 2002
    Posts
    1,264
    Hi Branden

    A formula such as:

    =COUNTIF(A:A,A1)<2

    would do that.

    Richard

  3. #3
    Registered User
    Join Date
    08-30-2007
    Posts
    28
    Thank you. Works perfectly. That is exactly what I was using before. Your a pal!

  4. #4
    all4excel
    Guest

    Question it works only in the adjoining cells/rows

    Quote Originally Posted by comptechbranden
    Thank you. Works perfectly. That is exactly what I was using before. Your a pal!
    i tried this formula it does work perfectly well in continuous rows

    For ex:-
    =COUNTIF(H:H,H1)<2

    It works when i have entered the same thing in cell h2 and cell h3 it pops the box retry cancel..

    However i can still enter the same thing in h4 which is alread there in h2..

    let's say i have 1 in h2 and 2 in h3 i should not be able to enter 1 and 2 both in h4....???? or else the whole purpose is defeated

  5. #5
    Valued Forum Contributor Richard Schollar's Avatar
    Join Date
    05-23-2006
    Location
    Hampshire UK
    MS-Off Ver
    Excel 2002
    Posts
    1,264
    That sounds like it's because you haven't applied the data validation to these cells, hence it won't apply. The reason why it still works in contiguous cells is because under Tools>Options>Edit tab you have "Extend data range formats and formulas" checked.

    Hence, if you select the entire column first and go Data>Validation and apply that formula, it will operate in all cells.

    Richard

  6. #6
    all4excel
    Guest

    Question Worked great

    Thanx it worked after using the way u explained

  7. #7
    Forum Contributor VBA Noob's Avatar
    Join Date
    04-25-2006
    Location
    London, England
    MS-Off Ver
    xl03 & xl 07(Jan 09)
    Posts
    11,988
    all4excel,

    Please read forum rules again. Start your own thread's next time.....and stop using I want as a title

    VBA Noob
    _________________________________________


    Credo Elvem ipsum etian vivere
    _________________________________________
    A message for cross posters

    Please remember to wrap code.

    Forum Rules

    Please add to your signature if you found this link helpful. Excel links !!!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1