autogenerate WO number

[flyingfree]

I need to autogenerate a number in a cell when other data in the row is filled out.

Sheet is basically:


Date Site Name several other columns of non relevant data Work Order Number


I need the WO number to be in the following format:


(4 digit site code)-1920-(next number for that site)


I have another sheet with a table in it that shows:


Site Name Site Code


How can I pull the site code from one sheet into the number based on the site name from a previous column entry then add in the fiscal year reference and a numerical count of Work orders for that site?


Thinking of adding a hidden column to change site name to site code then formula in the WO number column to read:

=”C2”,”-1920”,countifs(C:C,site code 1, C :C, site code 2)

Is that the correct use of the countif function? Is there an easier way? Perhaps a VB code with a variable that increases by one everytime the loop is run? We have over 25 site codes.

How can I easily convert the site name to a site code based on the table in the other sheet?

 

[Toadstool]

Hi Flyingfree,

Here I've got the Site Name/Site Code table in the same sheet (columns G and H) but not as a table (it can of course be on another sheet and in an Excel Table so it auto-extends and you can use Names instead of references).

When you enter a Site Name in column B then in column C it retrieves the Site Code from the table.

Column D makes the WO code along the lines of your suggested formula but please note the mix of absolute and relative addresses in the COUNTIF so you get an ascending count.

Cells C2 and D2 need to be copied and pasted down as far as the maximum row you'll ever use.

NOTE: This is a calculated WO so if you change the data in any previous rows then it recalculates the WO numbers.

	B	C	D	E	F	G	H
Site Name	Site Code	WO	Site Name	Site Code
S1	0101-1920-0001	S1
S2	0202-1920-0001	S2
S1	0101-1920-0002	S3
S1	0101-1920-0003
S2	0202-1920-0002
S1	0101-1920-0004
S1	0101-1920-0005
S1	0101-1920-0006

<tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]0101[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]0101[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]0202[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]0202[/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]0101[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]0303[/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]0101[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]

[TD="align: right"]0202[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]

[TD="align: right"]0101[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]

[TD="align: right"]0101[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]

[TD="align: right"]0101[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C2[/TH]
[TD="align: left"]=INDEX(Sheet1!$H$2:$H$30,MATCH(B2,Sheet1!$G$2:$G$30,0))[/TD]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]D2[/TH]
[TD="align: left"]=IF(C2="","",C2&"-1920-"&TEXT(COUNTIFS($C$2:C2,C2),"0000"))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[flyingfree]

INDEX...hmmm I shall have to look that command up, might come in handy another time. Thanks, I'll implement this and get back to you on the results.