Automatically create job number based on prefix, year,month and entry number

[TheShyButterfly]

Hello and thank you for taking your time to read this post.

I have tried searching the internet and this forum but have not found anything that I have been able to modify to my needs.

So here is what I need help with.

I have a spreadsheet (excel 2010) with multiple columns - for example, Job#(A1), Event type(B1),KPI data (C1),SNP Order# (D1), ... etc ... there is so much information that a new workbook is derived from a template each month.

I need VBA code (or macro) in Column A2 which will increment with prefix 'SRC' then the 'year' 'month' and then increment by 1 so the job number would look like this:


SRC201309-0001 (SRC Year2013 month09 - JobNumber0001)

As the spreadsheet is saved as a template, it would be great if each time a workbook is created from this template, that it would automatically base the first job number on the year and month on that day and with first job number.

It would be appreciative if the code could include that if Column B has empty field that the Column A field remain blank until data is entered in Column b field (I'd imagine for example that the IF statement would kick in there .... eg. if(B2="","", and here is where the job number would be formulated ....


[TABLE="class: grid, width: 12"]
<TBODY>[TR]
[TD][/TD]
[TD]A
[/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[TD]D
[/TD]
[TD]E
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]Job #
[/TD]
[TD]Event Type
[/TD]
[TD]KPI Data
[/TD]
[TD]SNP Order#
[/TD]
[TD]Weather
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD][/TD]
[TD]Earthquate
[/TD]
[TD]87
[/TD]
[TD]12112
[/TD]
[TD]Severe
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD][/TD]
[TD]Theft
[/TD]
[TD]93
[/TD]
[TD]12113
[/TD]
[TD]Overcast
[/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</TBODY>[/TABLE]

So that the sheet will look like this ...

[TABLE="class: grid, width: 13"]
<TBODY>[TR]
[TD][/TD]
[TD]A
[/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[TD]D
[/TD]
[TD]E
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]Job#
[/TD]
[TD]Event Type
[/TD]
[TD]KPI Data
[/TD]
[TD]SNP Order#
[/TD]
[TD]Weather
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]SRC201309-0001
[/TD]
[TD]Earthquate
[/TD]
[TD]87
[/TD]
[TD]12112
[/TD]
[TD]Severe
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]SCR201309-0002
[/TD]
[TD]Theft
[/TD]
[TD]93
[/TD]
[TD]12113
[/TD]
[TD]Overcast
[/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</TBODY>[/TABLE]


I hope I have provided enough information and not confused you.



Thanking you in advance
Theshybutterfly

 

[vaskov17]

put this in A2 and fill down:

="SRC" & YEAR(TODAY()) & TEXT(MONTH(TODAY()),"00") & "-" & TEXT(ROW()-1,"0000")

 

[TheShyButterfly]

put this in A2 and fill down:

="SRC" & YEAR(TODAY()) & TEXT(MONTH(TODAY()),"00") & "-" & TEXT(ROW()-1,"0000")

Thank you vaskov17 for your quick response ... and bravo, this worked a charm ... I did add a little more to it to the following and filled it down the whole column A with ...

=IF($B1="","","SRC"&YEAR(TODAY())&TEXT(MONTH(TODAY()),"00")&"-"&TEXT(ROW()-1,"0000"))

which means that if cell B2 is blank then Column A is also blank, but as soon as you put data in Column B the next 'Job number' will appear

You can't imagine how thrilled I am with your response .... this format can be used to generate many different types of numbers ... all people have to do is change the prefix (SRC) with their needs.

I assume that this could be coded via VBA ..... just not sure how to do that.

Having another look at your solution though (and I still love it) ... I am concerned that if the spreadsheet is opened up weeks down the line, that it will overwrite the existing job numbers with the date when the spreadsheet was opened ..... I think that's where the 'hard coding' of VBA would more advantageous to lock it down ... so it won't automatically change the data when the spreadsheet is opened at a later date.

Would you be able to help out with the VBA coding or tweak the formula so it is permanent ?
Thank you again for your time and effort ... it is definitely appreciated ...

Cheers,
TheShyButterfly

 

[vaskov17]

I guess I misunderstood your initial post and thought you wanted the job numbers to be updated every time the sheet opened. I'll come up with something in VBA and post it later, unless someone else beats me to it.

 

[TheShyButterfly]

Sorry if I was somewhat a little unclear ... I have thought of a temporary workaround using your solution

I have copied and pasted 'our' solution as value only .. and then just 'filled' it down creating the next sequential number ... it just won't look neat by having the numbers already entered, but at least they have something they can use and I just have to remember to do this next month if nobody has come up with a solution by then

Thank you again
Cheers,
TheShyButterfly

 

[tom_blamire]

be careful as by using the YEAR / MONTH & Today within the formula, the job number will constantly update itself. I would say VBA would best used in this scenario

 

[vaskov17]

Here is one possible solution:

1. In the template file use the formula in cells A2 down as far as you want:
=IF($B1="","","SRC" & YEAR(TODAY()) & TEXT(MONTH(TODAY()),"00") & "-" & TEXT(ROW()-1,"0000"))

2. In the VBA editor, on the top left you should see a list with all the sheets in the excel file that looks like this:
- Sheet1 (Sheet1)
- Sheet2 (Sheet2)
- Sheet3 (Sheet3)
- ThisWorkbook

Double click on ThisWorkbook and on the right side paste the following code:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Dim i As Long
    
    With Worksheets("Sheet1")
        If .Range("A" & Rows.Count).End(xlUp).Row > 1 Then
            For i = 2 To .Range("A" & Rows.Count).End(xlUp).Row
                If .Range("A" & i).Value <> "" Then
                    .Range("A" & i) = .Range("A" & i).Value
                Else
                    Exit Sub
                End If
            Next i
        End If
    End With
End Sub

Basically what the code does is when the file is being closed it looks in cells in "Sheet1" starting at A2 and going down. Any cell that has a value different from "" gets converted from a formula to whatever is in the cell. Once it hits a cell with "" as it's value it stops so if there is a row where column B has no value and column A is blank but lower rows have values it will not convert the rows below the blank. It's easy to change it to work until all rows with a formula in column A are checked.

If you want to use this in a sheet other than "Sheet1" you have to change a part of the code to reflect that.

Let me know if this works for you or if you want it handled in a different way.

It's important to not that people who have set their macro security level to block macros will not be able to use the file properly.

 

[TheShyButterfly]

Here is one possible solution:

1. In the template file use the formula in cells A2 down as far as you want:
=IF($B1="","","SRC" & YEAR(TODAY()) & TEXT(MONTH(TODAY()),"00") & "-" & TEXT(ROW()-1,"0000"))

2. In the VBA editor, on the top left you should see a list with all the sheets in the excel file that looks like this:
- Sheet1 (Sheet1)
- Sheet2 (Sheet2)
- Sheet3 (Sheet3)
- ThisWorkbook

Double click on ThisWorkbook and on the right side paste the following code:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Dim i As Long
    
    With Worksheets("Sheet1")
        If .Range("A" & Rows.Count).End(xlUp).Row > 1 Then
            For i = 2 To .Range("A" & Rows.Count).End(xlUp).Row
                If .Range("A" & i).Value <> "" Then
                    .Range("A" & i) = .Range("A" & i).Value
                Else
                    Exit Sub
                End If
            Next i
        End If
    End With
End Sub

Basically what the code does is when the file is being closed it looks in cells in "Sheet1" starting at A2 and going down. Any cell that has a value different from "" gets converted from a formula to whatever is in the cell. Once it hits a cell with "" as it's value it stops so if there is a row where column B has no value and column A is blank but lower rows have values it will not convert the rows below the blank. It's easy to change it to work until all rows with a formula in column A are checked.

If you want to use this in a sheet other than "Sheet1" you have to change a part of the code to reflect that.

Let me know if this works for you or if you want it handled in a different way.

It's important to not that people who have set their macro security level to block macros will not be able to use the file properly.

Wow, thank you ... and once again for the prompt response.

The code works great in the template (macro enabled template), however when I open a new workbook based on the template, it hardcodes A2 with the value of the formula you provided above, but when I enter in information in B3, there is no longer any formula to keep the sequential numbering going.


Ok, you are squeezing more info out of me ... and that's good, because if I tried to explain everything in one hit, everybody would be confused and wouldn't attempt to solve my problem.

I'll briefly explain how this workbook/spreadsheet is used.


The spreadsheet is in use 24/7 constantly recording/logging events/jobs that come into our center (at this stage only one person is accessing/working on the document at any one time).

'Sheet1' is used for recording the Who, What, Where, When, Which patrol was dispatched, onsite & offsite times, etc.

So it would be necessary that the job number is updated 'live' ... because the spreadsheet does not get closed until the next shiftworker takes over.

There is likely to be a need to escalate jobs to the higher power, resulting in the need for a SRC job reference number.

I'm sorry that this is drawing out your efforts to resolving my problem ... but I am truly grateful for all your efforts (I am already singing your praises )

I don't think that there is too much more info that I can give you that would be beneficial but in case you need to know the following ....
I do have a lot of calculations in the 'sheet1' (cells A - BZ), and in the other sheets I have a few macros within the same workbook which are used for populating cells in Sheet1 with information from other sheets in the workbook and for data analysis.

This is why a workbook is generated from the template each month because the file ends up being over 7 MB in size by the end of month entries and calculations.


I imagine you are thinking why not do all this in MS Access .... where I can run queries and setup reports easily ..... I have raised this with my manager numerous times and have presented and explained the benefits and efficiencies of using a 'proper' database .... I haven't stopped mentioning it from time to time ... one day he may see the light

See, if I had mentioned all that before .... I would have been put in the 'too hard/complicated' basket .... and I don't like being a 'basket case'

Thanking you again
TheShybutterfly

 

[vaskov17]

In the template once you put the formula into A2 how far down column A are you filling in the formula? On my machine, the template has the formula from A2 to A127 and it works fine for any new workbooks based on that file. I'd say for the template fill in the formula as far down as you want to make sure you have enough entries to last an entire month. That's not the best approach since if you have a lot of other calculations on the sheet it will slow it down.

I can probably come up with a macro that automatically adds the job number whenever data is added in column B if it would be easier than this.

 

[TheShyButterfly]

Hi Vaskov17, thank you for your quick response again.

I filled the formula down the to approximately 1000 rows. The formula goes in and when data is entered into B2, B3, etc, it does appear .... but if I open up the spread sheet the following month .. which I will have to do to check the entries and do the statistical analysis of the data, I would presume that the date in A2 will update to the date that I do the analysis ..... I've just changed my system date and opened the spread sheet and as I thought, it did update to the month that I set up on the system.

The job number will need to be hard coded (VBA'd) so it won't change when I open the sheet for the end of month of analysis. And it would be fantastic if you were to come up with the code/formula for setting the cells in column A to populate once data is entered in Column B.

Many many thanks again

Cheers,
TheShyButterfly