Data validation question

[Ted Rogers]

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]

Hi Ted,

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

=COUNTIF($A$1:$A$50,A1)=1

 

[Ted Rogers]

Hi Ted,

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

=COUNTIF($A$1:$A$50,A1)=1

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

 

[David McRitchie]

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