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

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" wrote in message ...
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

"David McRitchie" wrote in message
...
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

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" 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