Job Sheet Considerations
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.
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:
Select the cells value is to spread across on the first row.
Right click, select Format Cells, Alignment and Centre Across Selection and then copy down.
Formating for Inches (Fractions)
Select cell(s) to be formatted.
Right click cell(s), select Format Cells, Number
Select Category: Fraction
Select desired Type
.png?version=2&modificationDate=1544507754233&cacheVersion=1&api=v2)
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
Common Formula's used in BUZ
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) |
=bpLOOKUP() | Essentially a Vlookup with an extra variable for the search For a detailed explanation Click here | |
For help relating to othe pages that can be added to a job sheet click on one of the links below
Job sheet - BUZ 3 to BUZ Cloud
Errors that may occur
Why does my order not have a job tracking status?
=PROPER(cell reference) |  |
=MID(Cell reference,start number,amount of characters after the start number) =MID(H14,6,99) | |
=VLOOKUP | |
=MATCH | |
=IF | |
=IF(ISNUMBER(SEARCH("-",B2)),"E","") | If a cell contains a character return a value For the example on the left if cell B2 contains a - then show E in the cell that the formula is being written If cell B2 does not contain an E then this cell will be blank
|