Qualifying a generated value as unique across worksheets
 

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! :confused:

J


--
jloos
------------------------------------------------------------------------
jloos's Profile: http://www.excelforum.com/member.php...o&userid=36980
View this thread: http://www.excelforum.com/showthread...hreadid=567014

Qualifying a generated value as unique across worksheets
 

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" wrote:


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! :confused:

J


--
jloos
------------------------------------------------------------------------
jloos's Profile: http://www.excelforum.com/member.php...o&userid=36980
View this thread: http://www.excelforum.com/showthread...hreadid=567014

Qualifying a generated value as unique across worksheets
 

The C value is the cell that contains the generated code. So in the
first attempt I was check to see if the value contained in C23 was in
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 a
"Random Code Generator" which works through a series of formulas to
randomly generate a new code. What I can't get to work is the equation
that would then take that code and make sure it hasn't been used
before. If it hasn't been used, then the user is free to use that code
to assign to their line item otherwise they have to randomly generate
another code.

Does that make sense?


--
jloos
------------------------------------------------------------------------
jloos's Profile: http://www.excelforum.com/member.php...o&userid=36980
View this thread: http://www.excelforum.com/showthread...hreadid=567014

Qualifying a generated value as unique across worksheets
 

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

"jloos" wrote:


The C value is the cell that contains the generated code. So in the
first attempt I was check to see if the value contained in C23 was in
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 a
"Random Code Generator" which works through a series of formulas to
randomly generate a new code. What I can't get to work is the equation
that would then take that code and make sure it hasn't been used
before. If it hasn't been used, then the user is free to use that code
to assign to their line item otherwise they have to randomly generate
another code.

Does that make sense?


--
jloos
------------------------------------------------------------------------
jloos's Profile: http://www.excelforum.com/member.php...o&userid=36980
View this thread: http://www.excelforum.com/showthread...hreadid=567014