Job sheet - Formula's and Named Area's

There is 2 common formula’s that we use to get the data into the table

The first is a simple “display cell contents in another cell” or =Ref

=IF(ISBLANK(Data!BP12),"",Data!BP12)

That is used to get the data in the area highlighted in the red square, generally information that relates to the Main inventory item (Materials) and any thing that uses a Keyword in the group options (ITEMWIDTH, QTY, DESCN)

Second Formula option - the Lookup

Lookups are used to return the answers from the group options

I know those pictures are hard to see but there is a lot of data in the tab, when you have your jobsheet open it will be easier to see all the data.

Lookup example

For this example I have added a question “Opening or Make” to my group options and now I want to insert this into my jobsheet.

I have added a new column and in row 10 (which is normally hidden) I input the HEADING which aligns with the Group Options.

Then I copied the formula into the cell =bpLOOKUP(Data!$AV11,G$10,CustOrdOpt,6,FALSE)

One thing to note, do you see the G$10 that is in bold,

Make sure that is referencing the HEADING, my Heading is “SIZES” just for your information but your column might not be in G10 so make sure they align

Then you can copy paste the formula all the way down

So where is this information coming from?

This is the data tab and in yellow is a table called “CustOrdOpt” or Customer Order Options all the group option headings and answers are here

In red is the ID for the answers, which help us see which product we are getting the information from

and in Blue is the HEADINGS and Answers

Hopefully that all works for you

Job Sheet Considerations 

1. Job Sheets are processed left to right and top to bottom. Excel will sometimes compensate for not sticking to this rule but Aspose, the program BUZ uses to generate the PDF's from Excel does not. So this means that a sheet cannot refer to cells on another sheet to the right of the current one because it will not have it values calculated at that point in the process.

2. Cells in the body of the job sheet (the area that grows when number of line items exceed template) must NOT BE MERGED. 

To spread text across multiple cells:

1. 1. Select the cells value is to spread across on the first row.

   2. Right click, select Format Cells, Alignment and Centre Across Selection and then copy down.

      

Formating for Inches (Fractions)

1. Select cell(s) to be formatted.

2. Right click cell(s), select Format Cells, Number

3. Select Category: Fraction

4. Select desired Type

Note: If format doesn't work it is because Excel treats the number as text so type +0 on the end and that forces Excel to treat it as a number. 

Using Excel Name Manager

Named Areas are added using Name Manager, in Excel 2013 this is found on the FORMULAS tab near the middle.

1.

2. Select New

3.

4. The box opens and Type the Name, in Scope select the sheet being referenced and then click where the red arrow is pointing and select the cell containing the value. Click OK.

Job Sheet Formula Explanations

Formula used to produce a “due date”

for a Supplier / Factory  that doesn’t equal a weekend date and is 2 or 3 days prior to your Follow Up date (Install Date)

Step 1: Follow up date -3 for expected complete production date =LEFT(Data!BA5,10)-3

followup-date -3, picking up 10 to the left to only select the date

Step 2: Date Formula;    =IF(WEEKDAY(AB2,2)=6,AB2+2,IF(WEEKDAY(AB2,2)=7,AB2+1,AB2))  

If follow-up date equals Saturday then follow-up date plus 2 days

If follow-up date equals Sunday then follow-up date plus 1 day

For help relating to othe pages that can be added to a job sheet click on one of the links below

Job Sheet - Components

Job sheet - Labels

Job Sheet - Tickets

Job sheet - Barcode

Job sheet - BUZ 3 to BUZ Cloud

Pricing Scripts

Errors that may occur

Why does my order not have a job tracking status?

Why is my Job sheet empty?