This instruction will show you how to set up a basic job sheet
Job sheets are located in with 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 is a few things that help to know so that you can understand the Job sheet.
Take a look at a few Example job sheets, download them and we will eventually modify them to suit your business.
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 that are 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 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.
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 just yet, 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 you fill in all options available.
You can input your own details or make them up, either way this customer card can be useful for testing new products and or processes, rather than using a current customer.
Having this data in the customer card will help you allocate the information in the job sheet.
For help on creating a Lead or customer click on the link below.
Also 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, so that we can 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
You may need to process and approve if required, dont 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
Great work! Now 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 in 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.
Below is an example of a "populated" job sheet, by populated I mean has customer and product data.
For a detailed overview specific to the Data Tab click here
Lets break down the different areas in the Data tab
Data Settings
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 Information
Data in Row 5 (between Columns AV - DU) contain Customer and Sale 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 the time this help was written.
Key Word Headings
Key Word headings have a special significance and may generally serve an additional purpose like a calculation.
For more information relating Key Word Headings click here.
Row 11 - Between Columns AV and BW if all 3 Descriptions have been utilized in the Group inventory Questions. If one of the Descriptions is not used there will be less columns.
Not all of the product information is here so we tend to not 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 preform look up searches.
Group options - Cust OrdOpt
The Group Options area contains all the data that pertain with an order, this information will populate in a "Named Field" called CustOrdOpt - can be populate in Rows CC - CK. This is dependant on the use of the description fields and if they have been utilsed. This may change from product to product but will generally stay in the same area once the product has been setup.
Data explained
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
What do we do with all this information?
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
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
If you look at the table above I have put the Heading in Green (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 formula's, Click here to see them
Once you have setup 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 in 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 how big the table will be in each page (If a table exists).
First Row
Last Row
Right Column
The table is deisgned to expand if there is 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 is formula's in the rows below the table they will stay and will even populate, but the information will be a duplicate from an existing Row causing more products to be made then required.
If there is formulas on the right side that are not within the data table they will populate but will not expand if there is 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 its not exactly what you are expecting, this is common and you may need to edit the Job sheet several times to get the correct setup, remember keep to save copies so you can go back if needed.
Help!
Click here for some possible errors that may be occuring