These can be exported/imported/deleted through your Inv. groups
Before you start
There is a few things you need to know,
This instruction will show you how to set up a basic job sheet.
Job sheets are located within the product "Group" level.
To navigate there, you will need to go to settings → Group Settings → Groups tab.
Before you start
First, You don't need to be an
Excel guru, but there
are a few things that help you to know so that you can understand the Job sheet.
Look at a few Example job sheets, download them, and we will eventually modify them to suit your business.
Job Sheet Templates - Download a template here.
We have made a few templates.
Job sheet - the basic layout is a simple form with a table for the job sheet and check measure.
| View file | ||
|---|---|---|
|
Job Sheet - Multiple Example tabs
| View file | ||
|---|---|---|
|
Job Sheet
Fabric Cut
Tracks Pelmet
Tickets
Dispatch Label
Tube Cut
Tube Lookup
Check Measure
Deductions
Labels
Job Sheet - with Bill of Material has many example pages, including
Tube size chart example,
Parts List
Deductions tab with a few example formulas
Components sheet and an example for a full Bill of material.
You are welcome to modify the template and remove almost every tab that is not required. You will need the Data and Job sheet tab as a minimum.
| View file | ||
|---|---|---|
|
Now that you have opened the job sheet, I will explain a few things
The data that is populated in the Job sheet comes from several places
Information is gathered from
There are areas in the
Excel spreadsheet
called
"Named fields", which can make life easier
if you know how to use them
.
Job sheets contain a Macro, but you can not add your
macros
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
challenging to complete the Job sheet.
Step 1.
Upload a
template
Job sheet
.
Go to Setting → Inventory Settings → Groups (
Will 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
It doesn't matter which job sheet you add, nor do the headings matter too much; we can connect the customer data to the correct columns later.
Step 2.
Setting up a test customer
If you haven't already, Create a test customer/lead, making sure that ensuring you fill in all options available. available options.
You can input your details or make them up; this customer card can be helpful for testing new products and or processes rather than using a current customer.
Having this data in the customer card will help you allocate it the information in the job sheet.
For help on creating a Lead or customer, click on the link below.
Step 3aAlso, anywhere you see a "Notes" field for testing purposes, input something here, something that will remind you where you entered the information.
Step 3a
Create an Order
We need to generate data for the Job sheet to see how BUZ allocates this information.
Create a quote, again make sure every question is answered (even if it is not a mandatory question)
Step 3b
Finalising the order
Complete all the Finalize Questions.
Step 3c
Accept the
Quote, (ProcessQuote
You may need to process and approve if required; don't worry about any payments.
Once the order has been approved, print the job sheet
In the top right is a Print button - Print the Job sheet - excel
The Job Sheet
Great work! We have created an order with our required information; we are ready to look at the Job sheet.
Job sheet - Data Tab
Open the Excel file; this may be located in your "Downloads".
Once the document is open, you may be prompted to "Enable Editing" Click the button, and you will be able to make changes to the Job sheet.
If you see this error message, you will need to follow the instructions on the following link.
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.
Below is an example of a "populated" job sheet; by populated, I mean customer and product data.
For a detailed overview specific to the Data Tab, click here
Let's break down the different areas in the Data tab
Data Settings
The first thing you will notice is our Data Settings table
. we
won't worry about this for now, but we will
return to it later.
Customer and
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 &sales rep Information
Data in Row 5 (between Columns AV - DU) contain Customer and Sales rep information.
Getting this information to other tabs is as simple as =Data! + Cell number (example - Customer. Description would be =Data!BF5).
Commonly used (but not restricted to) cells are
Quote No. - Concatenate of BC5 & BD5
Customer Name - BF5
Address - Concatenate of BH5, BI5 & BK5
Email - BR5
Customer Order Reference - AX5
Sales Rep - CE5
Note: Please ensure you test any formulas in the setup stage; these cells may have moved or changed since this help was written.
Key Word Headings
Key Word headings have a special significance and may serve an additional purpose like a calculation.
For more information relating to Key Word Headings, click here.
Row 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 pt2if all 3 Descriptions have been utilized in the Group inventory Questions. If one of the Descriptions is not used, there will be fewer columns.
Not all of the product information is here, so we tend not to use this information wholly.
A significant constant is that the first Product is always on row 11, and the unique Identification key is in Cell AV, so commonly use AV11 to perform look-up searches.
Group options - Cust OrdOpt
The Group Options area contains all the data that pertain to an order; this information will populate in a "Named Field" called CustOrdOpt - which can be populated in Rows CC - CK. This is dependent on the use of the description fields and if they have been utilised. This may change from product to product but will generally stay in the same area once the product has been set up.
Data explained
So where is data coming from?
All the headings
you entered
into the group options populate
into the Job sheet with the answer
provided in the order.
Above is a small
snippet of the Job sheet and the Group options,
The Headings in Green,
Questions asked in the Quote process in Orange and
Answers in red
What do we do with all this information?
you are askingNow 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 Let's look at the formula's in formulas on the Job sheet page
A few common formula's formulas that we use in BUZ are
Formula |
|---|
Explanation | 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 |
formula 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 |
two 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
Product Table
This space is where all the product information will populate. A commonly used formula would be the bpLOOKUP; this will use the reference in the heading row (Row 12 in the image below) to find the answer selected in the order.
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
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 bIf you look at the table above, I have put the Heading in Green (the same heading you would have in the group options)
The formula's in this table can be customised to suit your needs,
The support team has provided a few examples of some useful formulas. Click here to see them
Once you have set up the table, you will need to define the table size in the Data Tab
Data Tab - Data Settings table
This is a quick guide for the Data Settings table.
You will need to enter the values of the table here so that the software knows where to populate the data.
Data Settings
Sheet name - relates to the corresponding Tab,
First Row, Last Row and Right Column
These values dictate the size of the table on each page (If a table exists).
First Row
Last Row
Right Column
The table is designed to expand if there are more products than lines. BUZ will use the formulas in the "Last Row" and copy them down.
Factors that may affect the population of information in the table → Job sheet not printing correctly
If there are formulas in the rows below the table, they will stay and even populate, but the information will be duplicated from an existing Row causing more products to be made than required.
If there are formulas on the right side that are not within the data table, they will populate but will not expand if there are extra products.
The example above has data in Row 20 and in Column H, which will cause the table to be inaccurate.
Include in PDF
Some of the Tabs are only required for calculations, like a deductions Tab or for printing Labels; these are not required to be printed out with the job sheet.
To select which documents are printable, use the value "TRUE" in the "Include in PDF" Column.
No. of Copy in PDF
This lets you set the value for how many copies of the Tab you need. There may be times when you require more than 1 Job sheet; if so, you can change the value in this column.
Save, Upload and Test
Go to Setting → Inventory Settings → Groups
Select the Group (Product) that you have been working on
Scroll to the heading Job Sheet (at the bottom of the page)
Select the file and press the "Save" Button
Go back to the test order and print another Job sheet.
Dont lose heart
Chances are that it's not exactly what you expect; this is common, and you may need to edit the Job sheet several times to get the correct setup; remember to keep saving copies so you can go back if needed.
Help!
Click here for some possible errors that may be occurring.
