Part Number Creator

[shelwin]

Hi I am wanting to create part numbers with this table, the sub group and the extra info columns are drop down lists and random part number column creates random numbers. The formula i am using in column a (part number) is =LEFT(B2,3)&C2&D2&E2&F2 but i now need to add a column next to the sub group with descriptions of the sub group codes so it need to be dynamic depending on the option chosen so the right description matches the sub group code and another column to do the same next to the extra info code but I also do not want the formula to read these columns so it does not affect the part number. I then need the part number created in the part number column to copy to a column added next to supplier code so i can then generate a bar code using a bar code font? Any help would be appreciated thanks.

Part Number	MAIN GROUP	sub group	extra info code	random part number	supplier code
AVVAME29418441	AV	VA	ME	294184​	41​
BEROME17926741	BE	RO	ME	179267​	41​

 

[jmacleary]

OK. The following I think represents what you want:

Book1
	A	B	C	D	E	F	G	H	I
1	Part Number	MAIN GROUP	sub group	extra info code	code description	random part number	supplier code	supplier description	barcode
2	AVVAME29418443	AV	VA	ME	Really long ME	294184	43	Joe Smith	*AVVAME29418443*
3	BEROME17926741	BE	RO	ME	Really long ME	179267	41	Fred Bloggs	*BEROME17926741*
Sheet3

Cell Formulas
Range	Formula
H2:H3	H2	=VLOOKUP(G2,supp_codes,2,FALSE)
I2:I3	I2	="*"&A2&"*"
A2:A3	A2	=LEFT(B2,3)&C2&D2&F2&G2
E2:E3	E2	=VLOOKUP(D2,info_codes,2,FALSE)

The 2 tables referenced are:

Book1
	L	M
1	info codes
2	ME	Really long ME
3	FR	Another desc
Sheet3

and:

Book1
	O	P
1	supp codes
2	41	Fred Bloggs
3	43	Joe Smith
Sheet3

 

[shelwin]

thank you for the quick reply John I will give it a try now

 

[jmacleary]

OK. You may have a bit of trouble with the lookup for the supplier code - make sure all the data is in the same format otherwise there may be a problem (e.g. numeric 41 is not the same as text 41 so the VLOOKUP would fail)