Page Comparison

Deductions in job sheets normally refer to a addition or subtraction to a Width, drop, depth or any figure in a job sheet.

Deductions can be useful when a job requires modification due to a fit (reveal or face) or if a control option affects the length.

There are 2 main methods used in BUZ but you are not limited to these methods, there are plenty of other formulas that excel has to offer.

If you have not downloaded a Jobsheet Click here

Add the Deductions Tab

Select the Plus symbol at the bottom of the screen to add a new Tab,

Rename the tab "Deductions"

Creating a List

Add the Names and Values that you will be using

Note if you are relying on an answer these need to match exactly as they would be in the Group options

Download the group options and use the answers listed.

Group Options

Deductions Tab in the Job sheet

Add Values that you wish to add or subtract

Named Field

Next we need to define the area by giving it a name

Select the area you wish to name, I suggest selecting the whole Column so that it is easier to add extra values in the named area at a later stage.

Go to "Formulas" in the menu bar and select "Define Name"

It should auto populate some information, if the box does not populate.

Name: Add a name for the Named field, try to keep it simple, with no spaces and each named field has to be different from the last named field if there is multiple named fields, (generally I used the HEADING from the Group options, just so I know which area the deduction is relevant to.

Select the Scope - Workbook (this way you don't restrict the named field to only one tab).

Comment: can be left blank.

Refers to: the area you wish to include in the deduction, generally it will consist of the answer's and value's but if you wish you can add other values or dependencies if you are familiar with excel formula's

Select "OK" and test that it worked

to do this de-select the area that was highlighted, or simply click another cell that isnt in the named field area.

Select the Cell Reference Box

Select the field you created

It will then highlight the area you had intended when you created it. If this is not the case you will need to start these steps again.

Repeat these steps as many times as you require with the other area's you wish to utilise.

Formula

Next we need to make a formula, I will provide you with a Basic formula but I will leave it to you to modify if required.

I always find with any formula's or modifications to a job sheet that it is easier to use a job sheet with data in it.

By data, I mean a jobsheet that has been printed out from an order, the jobsheet doesn't need to be perfect, it just needs to have some test data that helps you see if your formula will work.

Below is my test table, Column D will be using the "Width" and Column "M" will be the look up answer for the list we created.

I have put a few different answers so that I can see the results which will be added or subtracted and shown in column "F"

We then use a VLOOKUP to find the answer using the reference in column "M"

The formula I used here is

=D10-VLOOKUP(M10,CONTROLS,2,FALSE)

Explanation of the formula

=VLOOKUP(M10,CONTROLS,2,FALSE)

The VLOOKUP requires a few variables to work,

VLOOKUP - the type of formula
lookup value, in this scenario M10 which is the column that contains the answer we are looking for
Table array - The table name or defined area if you didn't name the table, I used CONTROLS
Col index number - for Column C the would be the first index number, but we want to return the second number to our formula
Range lookup
- FALSE means that it needs to be exact match
- TRUE will is an approximate match