+ Reply to Thread
Results 1 to 4 of 4

Data validation question

  1. #1
    Ted Rogers
    Guest

    Data validation question

    A colleague runs the car-park lottery for our organisation. She uses an
    Excel spreadsheet witha round 1500 entries. The sheet identifies each staff
    member by means of their exclusive payroll number. The lottery is run 4
    times a year. However, she has a problem in that when a member rings up to
    register sometimes neither her nor the member is able to tell if they have
    registered before. She wants to ensure that it is not possible to enter a
    person twice.

    So, what would be ideal, is when she has entered the payroll number if there
    is no pre-existing entry Excel will allow her to proceed to the next field,
    if there is this will be flagged up and further entry would not be
    permitted.

    I would be grateful if anyone could point me in the direction of a solution.

    Best wishes,

    Ted



  2. #2
    David McRitchie
    Guest

    Re: Data validation question

    Hi Ted,

    See Preventing Duplicates On Entry
    http://www.cpearson.com/excel/NoDupEntry.htm

    =COUNTIF($A$1:$A$50,A1)=1
    ---
    HTH,
    David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
    My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
    Search Page: http://www.mvps.org/dmcritchie/excel/search.htm


    "Ted Rogers" <[email protected]> wrote in message news:%[email protected]...
    > A colleague runs the car-park lottery for our organisation. She uses an
    > Excel spreadsheet witha round 1500 entries. The sheet identifies each staff
    > member by means of their exclusive payroll number. The lottery is run 4
    > times a year. However, she has a problem in that when a member rings up to
    > register sometimes neither her nor the member is able to tell if they have
    > registered before. She wants to ensure that it is not possible to enter a
    > person twice.
    >
    > So, what would be ideal, is when she has entered the payroll number if there
    > is no pre-existing entry Excel will allow her to proceed to the next field,
    > if there is this will be flagged up and further entry would not be
    > permitted.
    >
    > I would be grateful if anyone could point me in the direction of a solution.
    >
    > Best wishes,
    >
    > Ted
    >
    >




  3. #3
    Ted Rogers
    Guest

    Re: Data validation question


    "David McRitchie" <[email protected]> wrote in message
    news:[email protected]...
    > Hi Ted,
    >
    > See Preventing Duplicates On Entry
    > http://www.cpearson.com/excel/NoDupEntry.htm
    >
    > =COUNTIF($A$1:$A$50,A1)=1
    > ---
    > HTH,
    > David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
    > My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
    > Search Page: http://www.mvps.org/dmcritchie/excel/search.htm
    >

    Many thanks for that - this seems to work if changing a value in a cell but
    does not seem to work if adding a value ina blank cell (within the range in
    the formula)

    Unless I have missed something

    Ted



  4. #4
    David McRitchie
    Guest

    Re: Data validation question

    Hi Ted,
    try to avoid pasting values that destroys validatation, and
    formatting including conditional cell formatting.

    You can use this instead, so if you try to change a value above a
    cell with the same value you will get your validation error.

    Select all of column A with cell A1 as the activecell
    =COUNTIF($A:$A,A1)=1


    "Ted Rogers" <[email protected]> wrote
    > Many thanks for that - this seems to work if changing a value in a cell but
    > does not seem to work if adding a value ina blank cell (within the range in
    > the formula)
    >
    > Unless I have missed something
    >
    > Ted
    >
    >





+ 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