Serial number generation

jgopal

New Member
Joined
Dec 27, 2020
Messages
7
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
  2. MacOS
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.

Screen Shot 2020-12-27 at 11.40.43.png


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

New Member
Joined
Dec 27, 2020
Messages
7
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
  2. MacOS
*** 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.
 

Some videos you may like

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.

jgopal

New Member
Joined
Dec 27, 2020
Messages
7
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
  2. MacOS
*** For Job Openning Date
If you enter formula like this:
Excel Formula:
=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

Well-known Member
Joined
Oct 22, 2012
Messages
1,354
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
You 're Welcome & Good Luck.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,281
Messages
5,600,720
Members
414,401
Latest member
grenona2020

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top