Not all product groups are the same, and there may be a time when a Table, like the one in the example below, does not meet your requirements.
...
This is where Tickets could be your solution.
You can have a whole page dedicated to an individual product.
Commonly used for Doors, shutters and curtains but can be available for any product you require.
This allows more space for calculations or to show more information in a different layout.
Setup of "Tickets" Job Sheets
...
Tickets (1 per page)
Your Tab name needs to be "Tickets" plural
...
NOTE: This is a suggestion that will provide a working ticket. You do not need to follow this setup exactly; however, the use of other formulas or references to other tabs that are not the Data or job sheet has not been tested by BUZ and may not work. It will be your responsibility to conduct testing for anything outside the recommended setup shown on this page.
You can either download the template job sheet, which has an example of a ticket or add a new Tab to your current Job sheet and name it “Tickets”
...
When building the template, the layout is for one product per page. BUZ will add additional pages for orders that have multiple products.
Set the print area on the first Ticket only
...
, and have a second ticket setup with all formulas
...
relating to item 2.
...
The two formulas that we recommend are the INDEX Functions or Cell References. This allows the
...
For the first ticket cell "A1" will contain the item number
Using the below formula returns a reference in the cell you require in this case the "Item number"
=INDIRECT("'Data'!BN"&(10+A1))
The data settings should be set to your first printable area,even though you have the formulas in for the second item.
The below examples show
First row 1
Last row 33
Right column U
Download Job Sheet template/example here → JobSheets_SECD_BUZ_(1).xlsm
Curtain Ticket (2 or more per page)
Set print area for the first page which has 2 or more tickets. BUZ should automatically print the 2nd, 3rd, 4th 5th etc. tickets.
Each formula will use the Excel "INDEX" command, This allows the ticket to use a single Job Sheet line to go down the Ticket page.
Cell "A1" will contain the item number for the first ticket "1" and the second ticket will contain the item number 2 using the formula "=A1+1"
Using the below formula returns a reference in the cell you require
=INDEX(JobLines,A1,2) JobLines refers to a named area which is the line items on the Job Sheet tab, A1 the item line and 2 is the column where 1 is the first column in the JobLines named area. This named area needs to match the DataSettings for the Job Sheet tab and is best when starting in Column A, and must be first line item row, last line item row and rightmost column.
The DataSettings should be set to your first ticket area, even though the printed area is set to 2 or more tickets.
Download Job Sheet template/example here → 20160527.0916_Curtains_Job_Sheet_BUZ_BUZDemo.xlsm
...
information to populate in the required area and will produce results vertically, on the following pages rather than in a table, as you would see in the job sheet.
Item Number
Each ticket needs to contain the Item number. For the first ticket, this will be a static value; input the value 1 somewhere on the page; many formulas on this page will reference this cell.
...
For the following ticket/s, the reference will need to be cell reference+1
This means that the next page will provide references from the following product in the order (if applicable to the order)
...
Next, we want to define an area from where we will get the data. Generally, this will be on the Job Sheet.
Go to the Job sheet tab, and highlight the table where all the product information populates.
Next, select Formulas in the ribbon bar at the top, and while the area is highlighted below, select Define Name,name the table JobLines and press ok.
...
Now you have saved an area you can reference in any formula, and it will not move when copied somewhere else, which can be annoying.
...
Now we need to build the ticket,
Add the headings for the information you want to the page; mine is a list going down; feel free to customise your layout.
...
Next, I am adding my INDEX Functions.
...
Here is a quick explanation of the formula
=INDEX is the formula
Joblines refers to the table in the job sheet
A1 is where my Item reference is in the ticket; if your page reference is in a different cell, you will need to add this to the formula
...
the 1 on the end is the range; this is how many columns to the right the data is in.
...
For my layout 1 = Item No, 2 = Location, 3 = Material type, 4 = Material colour, etc.
...
Next, I will add in my Cell References for items that are not available in the Order table, things like Customer details or Order numbers and references.
You can find a complete list in the Data tab; the headings are in Row 4, and the Answers will be populated in Row 5
...
I have added
Order Number and reference =Data!BC$5&"."&Data!BD$5
...
Now we have added the initial item; we need to tell BUZ how to copy this for additional items
Copy the cells below
...
The item reference should be a formula; mine is =A1+1 as the item number is in A1
add all the formulas again, but instead of referencing A1, they need to reference the item number on page 2
...
Update the whole page; this page will be copied for any extra items included in an order
...
Finally, update the Datasettings Table to include the first, last row and last column
...
The data settings should be set to your first printable area, even though you have the formulas for the second item.