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
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
