Serial number generation

[jgopal]

Hello members,
Just a brief introduction to what I am trying to achieve. We are a SMB focusing on small scale logistics such as warehousing (WHG), packing (PKG), freight such as air and sea import (AIP/SIP), export (AXP/SXP), document clearance (CLG), transportation (TPT), etc..

We have an excel sheet used to open and close jobs. Currently, the cells are manually input however it leads to occasional human errors because of either being in a hurry to open the jobs or at times to close the job. Its a fairly simple spreadsheet with locked cells so that certain fields are not touched by anyone.

Job opening
Fields/cells that everyone has been given access to are Date, Job Number (manually input based on the previous number and job type, Client Name (drop down), Salesman account (drop down). These cells are filled in when opening the job and that's the how it flows.

Job closing
Cells such as invoice value of the job, its VAT, invoice date, invoice number (picked up from another locked sheet), and cost of doing the job. Once these job closing cells are populated, invoice is ready to be generated. Invoice is generated on a different excel sheet which pulls the required data from this worksheet based on the job number.

To make the job opening a simpler process, all we should do is enter the type of job (job code manually or from a drop down like, WHG, PKG, ) and the client name (from a drop down as it now). If these two are entered, then the date of opening job and job number is generated automatically. I was able to figure out some options from Mr Excel to do that based on IF cell has data, enter today's date and also partially on the auto generate of the job number using this command: (=IF($C4="","","ALN/"&MONTH(TODAY())&TEXT(YEAR(TODAY()),"00")&"/"&TEXT(ROW()-1,"000"))

However, I am still in a fix as I am able to only start from 003 and we will require to start from around 631 when we start Jan 2021. Also, I am not sure how to include the job type as per the current job number format we are using. e.g. Warehousing job - ALN/WHG/012021/631, Packing job - ALN/PKG/012021/632, Sea export job - ALN/SXP/012021/633 and so on and so forth.

Now for job closing, I would like to automate invoice number based provided all other cells for the job are filled and only if invoice date is also filled. Again, the invoice number has to start around 10861 and going up. Currently, there is another worksheet with invoice numbers, and when the job number is entered next to it, the cell in this worksheet does a VLOOKUP to that worksheet and populates the invoice number.

I know the requirements are just a few but my explanation above is quite a bit. My lookout is to keep the file as error free as possible and reduce staff's access to it as much as possible.

So appreciate if anyone can share their ideas with either possible formulas or macros that I should try to get this file optimized to its best.
If you require any further information, please let me know, I will share.

Thanks
J

 

[jgopal]

*** For Job Number
=IF(F4="","",CONCATENATE("ALN/",F4,"/",TEXT(MONTH(B4),"00"),YEAR(B4),"/",IF(ROW()=4,DD!$R$11,COUNTA($C$2:C3)+DD!$R$11)))
Part1
F4="",""
with this part if the row is empty, this column show empty.
CONCATENATE(
For joining Parts with together
1. "ALN/" 2. Cell F4 3. TEXT(MONTH(B4),"00") convert the month of Date to 2-digit number 4. YEAR(B4) this add year to part
5. IF(ROW()=4,DD!$R$11 this is work for fist row to take first number from sheet DD Cell of R11
6. COUNTA($C$2:C3)+DD!$R$11 Count number of row filled until that row and add it to First row number

ok. I am not familiar with IF(ROW()) syntax and the above type of COUNTA usage.
Not an issue, I will google and learn it and figure it out for better understanding and usage.

Thank you Maabadi.

 

[jgopal]

*** For Job Openning Date
If you enter formula like this:

=IF(F4<>"",if(B4="",TODAY(),B4),"")

only formula work on empty cell not that filled before.

*** For Invoice Number if you have fixed for each month you can prepare table for it based month name & other criteria and give them its numbers and with vlookup add first number to Last of Job number.

I will try the date formula and hope it works and stays constant. It will be an empty cell, it has to be filled when a job is opened showing that this is the date the job was opened...only thing it should not change the next day.

Invoice number too, I will try out your solution above or I think I have another way to modify the current method. Lets see which one works.

Thank you again.

 

[maabadi]

You 're Welcome & Good Luck.