These can be exported/imported/deleted through your Inv. groups
Before you start
There is a few things you need to know,
First, You don't need to be an excel guru but there is a few things that help to know so that you can understand the Job sheet.
The data that is populated in the Job sheet comes from several places
- Information is gathered from
- Customer Card
- Fabric/Material List
- Group Options
- Extras
- There are areas in the excel spreadsheet that are called named fields which can make life easier, if you know how to use them - Link to named fields help
- Job sheets contain a Macro but you can not add your own macro's
- BUZ uses Excel as a tool to get the coded information in and out of BUZ
- Before attempting to create and finalise a job sheet you must have the inventory and group options completed, without this information it will be very difficult to complete the Job sheet.
Step 1.
Download a Job sheet template from here and add the Job sheet to your product.
Go to Setting → Inventory Settings → Groups (should be the first tab)
Select the Group you want to add the Job sheet to
Scroll to the bottom of the screen
Click on the "select file" button and upload the template Job sheet
Step 2.
If you haven't already, Create a customer/lead, making sure that you fill in all options available. Having this data in the customer card will help you allocate it in the job sheet.
For help on creating a Lead or customer click on the link below.
Step 3a
Create a quote, again make sure every question is answered (even if it is not a mandatory question)
Step 3b
Complete all the Finalize Questions.
Step 3c
Accept the Quote, (Process and approve if required
In the top right is a Print button - Print the Job sheet - excel
The Job Sheet
Now we have created an order with our required information we are ready to look at the Job sheet.
If this is the first time you have opened a job sheet it will look quite alien, but don't worry it is not as complicated as it first appears.
First thing you will notice is our Data Settings table, we wont worry about this for now, but we will come back to it later.
Customer and Sales Rep Informaiton
There is 3 parts of this page that we will look at for this exercise
The first part that I want you to take from here is the data that is populated in Rows 4 & 5 - Between columns AV - DU
This is the customer information, which will include contact details, time the order was accepted and delivery address
Row 4 is the headings and
Row 5 is the data that is the customer information
Main Headings
The second part to take away from this page is Rows 10 & 11 - Between Columns AV and BW
This is the where some of the main components
Group Options - (CustOrdOpt)
The third and final part of the data tab is the CustOrdOpt - Rows CE - CP
Note - the columns may not be exactly in CE-CP, this is dependant on a few factors and may be a few columns left or right of this
Data explained pt2
So where is data coming from?
All the headings that you entered in to the group options populate in to the Job sheet with the answer that was provided in the order
Above is a small snipit of the Job sheet and the Group options,
The Headings in Green,
Questions asked in the Quote process in Orange and
Answers in red
So what do we do with all this information? you are asking
Now we need to organise the data into a table that helps you,
BUZ has provided a table that is commonly used but you don't have to use this layout if you have something different
Lets look at the formula's in the Job sheet page
A few common formula's that we use in BUZ are
| Formula | Explaination | Example |
|---|---|---|
| =Cell | by pressing = then selecting a cell this will show the value of a cell (that may be in another tab) in the cell you require Where you see Data! in the fomula like =Data!BF5 Mean that the cell value is being copied from the Data tab Generally all of your information will come from the data tab, unless you have deductions or reference tables, but we will get to that later. | =Data!BF5 |
| =Cell1&"."&Cell2 | this will add 2 cells together with a . between them | =Data!BC5&"."&Data!BD5 |
| =CONCATENATE(Cell1,".",Cell2) | essentially the same as above but works on older versions of excel | =CONCATENATE(Data!BC5,".",Data!BD5) |
| =TRIM(Cell1) | TRIM function returns a text value with the leading and trailing spaces removed. | =TRIM(Data!AX5) |
Example of the customer details area of the job sheet
Example of the product table
You will notice a =bpLOOKUP formula in the table quiet often, this is part of our marco, which I will explain here
So I will break the formula into its individual components
=bpLOOKUP(Data!$AV11,D$12,CustOrdOpt,6,FALSE))
bpLOOKUP( - Part of a macro but essentially a Vlookup to a named field in the data page
(Data!$AV11 – Lookup Value (product ID reference)
K$12 – Table array – (the value you are searching for)
CustOrdOpt – col-index-num (area you are searching)
6 range lookup
In green we have input the Heading that you would find in the Group options and the data sheet
| Formula | Explanation |
|---|---|
| = | denotes that a formula is active |
| bpLOOKUP( | Part of a macro but essentially a Vlookup to a named field in the data page This is telling the cell to find our answer b |
