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
 

Some videos you may like

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.

maabadi

Well-known Member
Joined
Oct 22, 2012
Messages
1,354
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
Welcome to MrExcel Message Board. Please Upload your example file with 10 row with XL2BB Addin at above of reply Section. OR upload it to free uploading site e.g. www.dropbox.com and input link here.
 

jgopal

New Member
Joined
Dec 27, 2020
Messages
7
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
  2. MacOS
Welcome to MrExcel Message Board. Please Upload your example file with 10 row with XL2BB Addin at above of reply Section. OR upload it to free uploading site e.g. www.dropbox.com and input link here.

Hi Maabadi, thank you for your quick response.
XL2BB didn't get installed on my MacBook, so I made a copy of the original file and skimmed the data down for 2 months of this year for you to see how it is currently.
Don't mind my long formulas as they probably are unconventional and there probably are better ways of doing it. They are all based on information I found on the internet from Mr. Excel and other forums alike.
Password for the sheets are 12345.

Once again, thanks for your time and help.

Regards
J
 

maabadi

Well-known Member
Joined
Oct 22, 2012
Messages
1,354
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
1. I Define Name Range for JobType & JobCode at Sheet DD. Also Input First Number for Job # and INV # at sheet DD.
JTS.xlsm
NOPQR
1
2JOB CODEJOB TYPEBOOKED BYCODE
3WHGWAREHOUSINGPADMINIPMI
4PKGPACKINGBABITHABTA
5CLGCUSTOMS CLEARANCESNALIMASMA
6AIPAIR IMPORT FREIGHTMANOJMNJ
7AXPAIR EXPORT FREIGHTAKBARAKR
8CATCLEARING & TRANSPORTATIONABHILASHABI
9SIPSEA IMPORT FREIGHT
10SXPSEA EXPORT FREIGHTColumn NameFirst Number
11WAPWAREHOUSING & PACKINGJOB # 2020375
12LULLOADING/UNLOADINGINV # 202010616
13LUPLOADING/UNLOADING/PACKINGJOB # 2021
14PJTPROJECTSINV # 2021
15TPTTRANSPORTATION
16CRSCROSS TRADE
DD


Then At Sheet 2020 I do this Changes
1. For column B at B4 , if you input Data daily, you can Input:
Excel Formula:
=IF(F4="","",TODAY())
2. I clear formula for column F and define Data validation for it (DropDown)
3. Define formula for Column C based column F & Date & ...
4. For Column E, Formula is Good , But you can also Use This Formula without Table at Sheet DD:
Excel Formula:
=IF(F4="","",LEFT(D4,3)&"XYZ000"&RIGHT(D4,1)
5. Also I modified very little your formula at INV Total & Profit/Loss
6. And add formula for INV #
7. Also note if you have empty row at middle , Numbers don't count rows and continue based order, not jumping e.g. from 9 to 11 or 383 to 385.
Formula you can see Under of each sheet:
JTS.xlsm
ABCDEFGHIJKLMNOP
1#DATEJOB #CLIENT NAMECODETYPEINV VALUEVAT VALUEINV TOTALADVANCEINV DATEINV #DISCOUNTCOSTPROFIT/LOSSACCOUNT
2JANUARY 2020
3
411/1/2020ALN/WHG/012020/375ABC1ABCXYZ0001WHG620.0031.00651.000.001/1/2020106160.00237.93382.07ABHILASH
521/1/2020ALN/WHG/012020/376ABC2ABCXYZ0002WHG300.0015.00315.000.001/1/2020106170.0098.78201.22BABITHA
631/1/2020ALN/WHG/012020/377ABC3ABCXYZ0003WHG150.007.50157.500.001/1/2020106180.0052.9597.05BABITHA
741/1/2020ALN/WHG/012020/378ABC4ABCXYZ0004WHG930.0046.50976.500.001/1/2020106190.00169.26760.74ABHILASH
851/1/2020ALN/WHG/012020/379ABC5ABCXYZ0005WHG372.0018.60390.600.0031/1/2020106200.00100.00272.00ABHILASH
961/1/2020ALN/WHG/012020/380ABC1ABCXYZ0001WHG303.8515.19319.040.0031/1/2020106210.0060.00243.85ABHILASH
1071/1/2020ALN/WHG/012020/381ABC2ABCXYZ0002WHG372.0018.60390.600.0031/1/2020106220.0095.00277.00ABHILASH
1181/1/2020ALN/WHG/012020/382ABC3ABCXYZ0003WHG665.5733.28698.850.0031/1/2020106230.00210.00455.57PADMINI
1291/1/2020ALN/WHG/012020/383ABC4ABCXYZ0004WHG2,573.70128.692,702.390.0031/1/2020106240.001,239.701,334.00AKBAR
1310    
14115/1/2020ALN/CLG/012020/384ABC1ABCXYZ0001CLG7,300.1220.507,320.620.007/1/2020106250.005,565.621,734.50PADMINI
2020
Cell Formulas
RangeFormula
H9H9=G9*5%
E14,E4:E12E4=IFERROR(VLOOKUP(D4,DD!B:C,2,),"")
C4:C12C4=IF(F4="","",CONCATENATE("ALN/",F4,"/",TEXT(MONTH(B4),"00"),YEAR(B4),"/",IF(ROW()=4,DD!$R$11,COUNTA($C$2:C3)+DD!$R$11)))
C13:C14C13=IF(F13="","",CONCATENATE("ALN/",F13,"/",TEXT(MONTH(B13),"00"),YEAR(B13),"/",IF(ROW()=4,DD!$R$11,COUNTA($F$2:F12)+DD!$R$11)))
I4:I14I4=IF(G4="","",G4+H4-M4)
L4:L14L4=IF(F4="","",IF(ROW()=4,DD!$R$12,DD!$R$12+COUNTA($F$3:F3)))
O4:O14O4=IF(F4="","",G4-SUM(M4:N4))
Cells with Data Validation
CellAllowCriteria
D4:D14List=DD!$B$3:$B$202
P4:P14List=DD!$Q$3:$Q$8
F4:F14List=JOBCODE
 
Solution

maabadi

Well-known Member
Joined
Oct 22, 2012
Messages
1,354
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows

ADVERTISEMENT

JTS.xlsm
ABCDEFGHIJKLMNOP
15125/1/2020ALN/CLG/012020/385ABC2ABCXYZ0002CLG2,612.0020.502,632.50700.0014/1/2020106260.001,560.001,052.00PADMINI
16135/1/2020ALN/PKG/012020/386ABC3ABCXYZ0003PKG150.007.50157.500.008/1/2020106270.001.00149.00ABHILASH
17146/1/2020ALN/CLG/012020/387ABC4ABCXYZ0004CLG6,546.0010.006,556.003,000.006/1/2020106280.004,244.002,302.00PADMINI
18157/1/2020ALN/CLG/012020/388ABC5ABCXYZ0005CLG18,789.0017.5018,806.5011,520.0023/1/2020106290.0017,902.00887.00AKBAR
191619/1/2020ALN/CLG/012020/389ABC1ABCXYZ0001CLG2,215.5026.502,242.00500.0022/1/2020106300.001,978.50237.00BABITHA
201720/1/2020ALN/WHG/012020/390ABC2ABCXYZ0002WHG581.0029.05610.050.0025/1/2020106310.0062.50518.50ABHILASH
211823/1/2020ALN/SXP/012020/391ABC3ABCXYZ0003SXP7,181.730.007,181.730.0027/1/2020106320.005,950.971,230.76BABITHA
221923/1/2020ALN/AXP/012020/392ABC4ABCXYZ0004AXP3,950.000.003,950.000.0026/1/2020106330.002,850.001,100.00BABITHA
232026/1/2020ALN/SXP/012020/393ABC1ABCXYZ0001SXP13,634.500.0013,634.500.0029/1/2020106340.0011,613.932,020.57BABITHA
242126/1/2020ALN/WHG/012020/394ABC2ABCXYZ0002WHG500.0025.00525.000.0031/1/2020106350.00140.00360.00AKBAR
252229/1/2020ALN/CLG/012020/395ABC3ABCXYZ0003CLG6,924.5615.006,939.560.0030/1/2020106360.005,737.561,187.00PADMINI
262329/1/2020ALN/CLG/012020/396ABC4ABCXYZ0004CLG7,446.5618.107,464.660.0030/1/2020106370.006,109.561,337.00PADMINI
272429/1/2020ALN/CLG/012020/397ABC5ABCXYZ0005CLG5,816.0612.505,828.560.0030/1/2020106380.005,341.56474.50PADMINI
282529/1/2020ALN/WHG/012020/398ABC1ABCXYZ0001WHG600.0030.00630.000.0029/1/2020106390.00215.00385.00AKBAR
2926     
3027     
3128     
3229     
3330     
2020
Cell Formulas
RangeFormula
C15:C33C15=IF(F15="","",CONCATENATE("ALN/",F15,"/",TEXT(MONTH(B15),"00"),YEAR(B15),"/",IF(ROW()=4,DD!$R$11,COUNTA($F$2:F14)+DD!$R$11)))
E15:E33E15=IFERROR(VLOOKUP(D15,DD!B:C,2,),"")
I15:I33I15=IF(G15="","",G15+H15-M15)
L15:L33L15=IF(F15="","",IF(ROW()=4,DD!$R$12,DD!$R$12+COUNTA($F$3:F14)))
O15:O33O15=IF(F15="","",G15-SUM(M15:N15))
Cells with Data Validation
CellAllowCriteria
D15:D33List=DD!$B$3:$B$202
P15:P33List=DD!$Q$3:$Q$8
F15:F33List=JOBCODE



JTS.xlsm
ABCDEFGHIJKLMNOP
4239     
4340     
44TOTAL90,534.15546.5191,080.660.0071,535.8218,998.33
45FEBRUARY 2020
46
4711/2/2020ALN/WHG/022020/399ABC1ABCXYZ0001WHG580.0029.00609.000.001/2/2020106400.00220.00360.00ABHILASH
4821/2/2020ALN/WHG/022020/400ABC2ABCXYZ0002WHG300.0015.00315.000.001/2/2020106410.0098.78201.22BABITHA
4939/2/2020ALN/CLG/022020/401ABC3ABCXYZ0003CLG6,329.5612.506,342.060.0010/2/2020106420.005,842.56487.00PADMINI
5041/2/2020ALN/WHG/022020/402ABC4ABCXYZ0004WHG870.0043.50913.500.001/2/2020106430.00130.00740.00ABHILASH
2020
Cell Formulas
RangeFormula
C47:C50,C42:C43C42=IF(F42="","",CONCATENATE("ALN/",F42,"/",TEXT(MONTH(B42),"00"),YEAR(B42),"/",IF(ROW()=4,DD!$R$11,COUNTA($F$2:F41)+DD!$R$11)))
E47:E50,E42:E43E42=IFERROR(VLOOKUP(D42,DD!B:C,2,),"")
I47:I50,I42:I43I42=IF(G42="","",G42+H42-M42)
L47:L50,L42:L43L42=IF(F42="","",IF(ROW()=4,DD!$R$12,DD!$R$12+COUNTA($F$3:F41)))
O47:O50,O42:O43O42=IF(F42="","",G42-SUM(M42:N42))
M44:O44,G44:I44G44=SUM(G4:G43)
Cells with Data Validation
CellAllowCriteria
D42:D43List=DD!$B$3:$B$202
D47:D50List=DD!$B$3:$B$202
P44List=#REF!
P47:P50List=DD!$Q$3:$Q$8
F47:F50List=JOBCODE
F42:F43List=JOBCODE
 

jgopal

New Member
Joined
Dec 27, 2020
Messages
7
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
  2. MacOS
Hi Maabadi,

WOW, that is a lot of stuff for me to do. Let me try applying it and then I will revert.
It may take some time for me to apply your recommendations, so please bear with me till I update and revert.

Your point 7: the empty row in between is the kind of errors that we encounter. A job was opened but there was a number duplication or error in the format...hence deleted the whole entry and reopened with the correct format later.

Also, I don't know if you missed my initial requirement for opening job or may be I wasn't clear enough on the below part
"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."

What I would like to achieve is I/we will choose job type, like WHG, PKG, etc and based on the job type, a new job number gets generated OR first field to be populated could be the client from the drop down, then the job type which generates the job number. I mean whatever is easily doable. That was what I was trying to do in the worksheet 2021 but go stuck and hence reached out.

I thank you for all of your above efforts as what started of a simple manual entry file couple of years back will become close to 95% automated, easy, error free and secured.

Regards
J
 

jgopal

New Member
Joined
Dec 27, 2020
Messages
7
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
  2. MacOS

ADVERTISEMENT

Hi Maabadi,

I was reading your message again and understood that you did incorporate the type based job generation.
Please ignore my previous message regarding that. Sorry about that.

Regards
J
 

jgopal

New Member
Joined
Dec 27, 2020
Messages
7
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
  2. MacOS
Hi Maabadi,

I finished applying your recommendations and the main thing I required which was the job number auto generation is working brilliantly.
However, I have some concerns on the date of job opening as well as the invoice number generations as well. I will list down the concerns with an explanation of the situation.

1. Job Number - formula and solution is working perfectly.
I am unclear on the formula application strategy. If you could please explain the logic of its working, so that I can apply it accordingly for the following months.

2. Job opening Date
If we use the today formula, wouldn't it keep changing the date every date to the present date value?
This is what I am assuming... I open a job (375) today, it applies today's date (28/12/2020).
When I open the file tomorrow (29/12/2020) to open another job, wouldn't the formula automatically update 375's date with 29/12/2020?

3. Invoice number
All jobs are not closed in consecutive order and so invoices will not be in consecutive order.

For example, certain freight jobs can be closed only once the shipment arrives or reaches at its destination which could be anywhere between 30-70 days. So the invoicing will happen only after that. On the file shared with you, an example would be jobs from 379-383 which is invoiced at the end of the month but they are opened at the start of the month. This is because they are all WHG jobs and they are opened at the start of every month and then as some clients pay in advance, they are invoiced on the 1st of the month whereas the other clients pay at the end of the month, so they are invoiced at the end of the month.

So that is why I am using the other INV NO sheet wherein the job that has to be invoiced is input there and the same is VLOOKUP in the 2020 sheet. This is done as part of closing the job. Invoice Date (closing date) on 2020 is updated and JOB number is updated on INV NO worksheet. This way the invoice numbers will sync up with the invoice date. Otherwise (we faced this situation earlier), we will have an invoice number 0102 generated on 28/12/2020 and 0101 generated on 29/12/2020. This cannot be possible as the 0101 should have been generated on 28/12 and 0102 should be on the next date or the next job when it is closed. Basically, invoice number column will not be in consecutive order and it should generate the number when invoice date or some other important parameter (as a control measure) is fulfilled to ensure the job is closed.
 

maabadi

Well-known Member
Joined
Oct 22, 2012
Messages
1,354
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
*** 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
 

maabadi

Well-known Member
Joined
Oct 22, 2012
Messages
1,354
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
*** 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.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,278
Messages
5,600,695
Members
414,400
Latest member
Damocles2021

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