Qualifying a generated value as unique across worksheets

[jloos]

Hi

I worked on creating a nice random code generator (three digits,
numerical and alpha) however now I need to find a formula that will
check to see if the code created is unique across several worksheets.

I've tried two methods thus far:

1. First Attempt - Use a IF/AND/Not Equal To formula

=IF(AND(C23<>('DNU Priority Codes'!A2:'DNU Priority
Codes'!A1007),C23<>('Email Flyer Priority Codes 2006'!A2:'Email Flyer
Priority Codes 2006'!A980),C23<>('Tradeshow and Event
Codes'!A2:'Tradeshow and Event Codes'!A1003),C23<>('List
Codes'!A2:'List Codes'!A1002),C23<>('Priority Code
Archive'!A2:'Priority Code Archive'!A1002)),"Not OK","OK")

2. Second Attempt - Tried a COUNTIF formula just to see if it'd work
with even just one of the sheets:

=IF(COUNTIF('DNU Priority Codes'!$A$2:'DNU Priority
Codes'!A1007,C1)>1,"Not Ok","Ok")

Neither of them work. I don't get an error, just the "Ok" even when I
know for a facts that the code is a duplicate. I cannot seem to find
where the formulas are going wrong.

Please Help!

J

 

[Guest]

I'm guessing all your generated codes are in column A for each sheet, but I
don't understand why C1 and C23 are involved. Can you explain what values in
column C represent?

-Simon

 

[jloos]

The C value is the cell that contains the generated code. So in th
first attempt I was check to see if the value contained in C23 was i
the A columns of any of the other worksheets.

As a back up, I have several worksheets containing codes (in column A
that are assigned to different things. Then in one worksheet I have
"Random Code Generator" which works through a series of formulas t
randomly generate a new code. What I can't get to work is the equatio
that would then take that code and make sure it hasn't been use
before. If it hasn't been used, then the user is free to use that cod
to assign to their line item otherwise they have to randomly generat
another code.

Does that make sense

 

[Guest]

I don't see anything wrong with the formula for the second attempt. Is your
C value and your column A both in numbers or both in text? If one is number
and the other is text, that might be a reason why it's not working.

-Simon