Preventing ID numbers being used twice

[NDBC]

I have 8 worksheets where the id tag numbers for the competitors are entered.
Each worksheet contains the entries from one class (eg a grade, b grade, c
grade etc) In theory as they all contain basically the same format I could
put the lot on one worksheet with one class below the other and use data
validation. I don't want to do this as it is not that easy for new users to
quickly decipher what class a competitior was in.

What I need to check is that when a ne id number is entered in say the a
grade worksheet that this id number has not already been used in the other
sheets for b to h grade. What I have done is create an extra workshheet with
the appropriate formulas dependent on each of the individual class worksheets
that contains all the different classes data on the one worksheet. I then
used a custom data validation formula to check if there was more than one of
any id tag number. I was hoping that when I typed in the tag number in one of
the class worksheets the error from the combined page would come up.

It doesn't. The repeated tag number also ends up in the combined worksheet
as well. The validation does work if i type the number in on the combined
worksheet. I can write code that will do what I want but is there a way using
formulas to bring back an error from another worksheet.

Thanks

 

[JLatham]

Without using a helper column to check the use of the entry on the other
sheets I don't think you can do this without VBA code. Remember that you can
have a value or a formula in a cell, but not both at the same time.

 

[NDBC]

Thanks for that. I hadn't considered a helper column. I have now done it with
code anyway and it is a lot tidier (to me anyway).