Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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! J -- jloos ------------------------------------------------------------------------ jloos's Profile: http://www.excelforum.com/member.php...o&userid=36980 View this thread: http://www.excelforum.com/showthread...hreadid=567014 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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! J -- jloos ------------------------------------------------------------------------ jloos's Profile: http://www.excelforum.com/member.php...o&userid=36980 View this thread: http://www.excelforum.com/showthread...hreadid=567014 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how do i find unique avg buy prices in multiple group of buys/sell | Excel Worksheet Functions | |||
Combining worksheets on a unique key column | Excel Worksheet Functions | |||
Duplicate Worksheets | Excel Discussion (Misc queries) | |||
Changing a Link Mid-way Across Worksheets | Excel Worksheet Functions | |||
joining to worksheets or filtering not sure of correct terminology | New Users to Excel |