No Duplicates

[rbeck]

I am working with 3 worksheets in the same book. I do not want the same part number duplicated in the three worksheets. If a part number is used on sheet 1 I do not want that same part number to be used again within those three sheets.
Please help
Thanks

[martindwilson]

are you saying that you are adding to other sheets and dont want to reuse a partnumber or they already exist and you want to check?

[rbeck]

I am using a lot of part numbers on three different sheets. The part number can only be used once on the sheets weather it be on sheet one, two or three.
I would like a note or warning the number was already used or is being duplicated.

[martindwilson]

OK assuming part numbers are always in col A sheets1 2 and 3
create 3 named ranges referring to as many cells in column as you think you'll reasonable need say
A1:A5000
then conditional format each column A (again only as many cells as you reasonably need) with
"formula is"
and enter
=COUNTIF(range,A1)+COUNTIF(range2,A1)+COUNTIF(range3,A1)>1
pick a cell colour.
now duplicates in any cell in col A of any sheet will change colour on entry
see attached
to view my named ranges click insert/name and you will see names martin1,2,3 click on any of them to see which cells they refer to.

[JBeaucaire]

I think that same approach can be used in a Data Validation on the A columns as well, just use a Validation of Custom: Formula:

=COUNTIF(range,A1)+COUNTIF(range2,A1)+COUNTIF(range3,A1)=0

Then on the Error Alert tab a little message that says, "The part number you entered has been used already."

So this would flat out stop you rather than just lighting up the cell with a color.

[martindwilson]

tried tht jb and it doesn't want to know
but is should be =1 to work anyway
but it wont even work with a single range name (,at least in '97)
range martin1 = a1:a5000
=COUNTIF(martin1,A1)=1 doesnt work
=COUNTIF($a$1:$a$5000,A1)=1 does