Ensure that a unique record is entered

Validating unique records is somewhat straightforward, however I need to ensure that a unique entry is made in one column if there is a duplicate in another column. Let me explain:

I have the following columns in my spreadsheet:

• Serial Number
• Repaired (Yes/No)

There will be duplicate entries in the serial number column, but there must never be more than one Yes record against each serial number in the Repaired column.

The following example is allowed:

Valid.gif

The following example is not allowed:

invalid.gif

Note: In the second example, there are two Yes entries against serial number 1000.
Typically, when this occurs, the user goes to the previous Yes entry for that serial number and changes the record to No.

Data is entered into this spreadhseet row by row. The user needs to be alerted when a duplicate Yes record is entered into the Repaired column. Even better, place the cursor at the previous Yes record so that it can be changed to No. Under no circumstances can I leave the spreadsheet in a state where there are two Yes entries for each serial number.

I don't want to use VB for this.

Can anyone think of a function for this?

Thank you

See if the validation in the attachment does what you're looking for.