N
narrator
I'd love some help with this tricky question please.
When I reference a random generation formula in sheet 1 to insert value in
sheet 6, i need each instance in sheet 6 to show a different outcome from the
random generation.
The formula in sheet 6 uses a VLOOKUP because the formula is each item in
sheet 1. Hence, I can't simply copy the formula from sheet 1, because to
handle the different iterations would mean maybe 30 formulas combined into
one.
If it helps, here's what I'm doing:
Sheet 1 is a list of 240 Product Types and the rules for creating product
codes.
Sheet 1 Column A contains 2 letter Product Type Codes
Sheet 1 Column G contains random Generated code based on different formulas
for different product types. (240 product types and with many using the same
formula, means about 30 different formulas down through column G.
EG: Product Type ES has a prefix MQ, 3 letters and 2 numbers (eg MQABC12).
Column G generates a random code specific to these parameters, resulting in,
say MQNNM74. Whereas Product Type BT has a prefix WGL, 4 numbers and a letter
(eg WGL1234A). Hence a different formula is used to generate a random product
number.
That sheet works beautifully.
Sheet 6 contains a list of the inventory, about 50,000 items.
Column K contains a list or the manufacturers product categories, given in 4
to 7 letters. Using "LEFT(K12,2)" I can extract the first 2 letters which I
use in the VLOOKUP to sheet 1 (Like "ES" in the 1st example above). The
VLOOKUP finds ES (the Type code) on sheet 1 and then grabs the randomly
generated product code from column G in sheet 1.
But when I do this for like products, the code remains the same for all
products. I need the formula in sheet 1 column G to either recalculate each
time a cell uses it, or to effectively transpose to sheet 6 so that each
instance of the same product type generates a different product code.
Hope i haven't been too wordy
And thanks in advance
When I reference a random generation formula in sheet 1 to insert value in
sheet 6, i need each instance in sheet 6 to show a different outcome from the
random generation.
The formula in sheet 6 uses a VLOOKUP because the formula is each item in
sheet 1. Hence, I can't simply copy the formula from sheet 1, because to
handle the different iterations would mean maybe 30 formulas combined into
one.
If it helps, here's what I'm doing:
Sheet 1 is a list of 240 Product Types and the rules for creating product
codes.
Sheet 1 Column A contains 2 letter Product Type Codes
Sheet 1 Column G contains random Generated code based on different formulas
for different product types. (240 product types and with many using the same
formula, means about 30 different formulas down through column G.
EG: Product Type ES has a prefix MQ, 3 letters and 2 numbers (eg MQABC12).
Column G generates a random code specific to these parameters, resulting in,
say MQNNM74. Whereas Product Type BT has a prefix WGL, 4 numbers and a letter
(eg WGL1234A). Hence a different formula is used to generate a random product
number.
That sheet works beautifully.
Sheet 6 contains a list of the inventory, about 50,000 items.
Column K contains a list or the manufacturers product categories, given in 4
to 7 letters. Using "LEFT(K12,2)" I can extract the first 2 letters which I
use in the VLOOKUP to sheet 1 (Like "ES" in the 1st example above). The
VLOOKUP finds ES (the Type code) on sheet 1 and then grabs the randomly
generated product code from column G in sheet 1.
But when I do this for like products, the code remains the same for all
products. I need the formula in sheet 1 column G to either recalculate each
time a cell uses it, or to effectively transpose to sheet 6 so that each
instance of the same product type generates a different product code.
Hope i haven't been too wordy
And thanks in advance