Excel Help (No doubt it's a simple fix)

Soldato
Joined
4 Apr 2004
Posts
5,625
Location
Pontypridd
I'm trying to set up an inventory file. What I need is for the product number to sequentially change for me, I thought this would be easy (autofill) but as the product number contains letters it does not want to play.

Essentially what I'm looking for is this

ITM-0001
ITM-0001-Small
ITM-0001-Medium
ITM-0001-Large
ITM-0001-XL
ITM-0002
ITM-0002-Small
ITM-0002-Medium
ITM-0002-Large
ITM-0002-XL
ITM-0003
ITM-0003-Small
ITM-0003-Medium
ITM-0003-Large
ITM-0003-XL

.... Down to 380.

Can someone help?
 
Soldato
Joined
6 Oct 2004
Posts
18,514
Location
Birmingham
Best bet is probably to create a column which contains the numbers, then in the product number column use a formula to concatenate the number with the rest of the product no
 
Soldato
OP
Joined
4 Apr 2004
Posts
5,625
Location
Pontypridd
Best bet is probably to create a column which contains the numbers, then in the product number column use a formula to concatenate the number with the rest of the product no

Problem I'm running into here is when I create the sequential numbers I'm using 0001, 0002, when using the forumla it converts them back to 1, 2 ect
 
Associate
Joined
24 Jun 2008
Posts
1,168
Completely over the top but:
Make column A a number up to what ever and copy this in to column b
Code:
="ITM-" & TEXT(ROUNDUP(A1/5,0),"0000") & CHOOSE(IF(MOD(A1,5)=0,5,MOD(A1,5)),"","-Small","-Medium","-Large","-XL")
 
Last edited:
Back
Top Bottom