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.

can do various tasks. We call them Deductions, but they can also do other tasks that aren't necessarily subtracting.

Standard deductions include but are not limited to

Subtraction
Addition
Look up in a table (Vlookup or Hlookup)
Look up in a Grid (Index and Match)

Deductions can be

useful

helpful 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

Methods

As with anything in Excel, you can use various methods and formulas; below is one option, but you don't need to use this one if it doesn't suit you.

Steps

Add the Deductions Tab

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

Rename the tab "Deductions"

Image Modified

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 OptionsImage Removed

Image Added

Deductions Tab in the Job sheet

Image Modified

Add Values that you wish to add or subtract

Image Modified

Named Field

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

Note: you can skip this step and input the cell range into the formula, but if you move the formula, the range may move and not cover the correct area.

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.

Image Modified

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

Image Removed

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

Name:

Image Added

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

are multiple named fields

,

(generally, I used the HEADING from the Group options

, just

so that I know which area the deduction is relevant to.

Select the Scope - Workbook (this way you don't restrict

Once you have added 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

Image Removed

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

Image Modified

Select the field you created

Image Modified

It will then highlight the area you had intended when you created it

. If

; if this is not the case, you will need to start these steps again.

Image Modified

Repeat these steps as

many times

often as you require with the other

area's

areas 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 that with any

formula's

formulas 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

job sheet that has been printed out from an order

,

; the

jobsheet

job sheet 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

use the "Width", and Column "M" will be the

look up

lookup 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"

Image Modified

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)Image Removed

Image Added

Explanation of the formula

=VLOOKUP(M10,CONTROLS,2,FALSE)

Image Modified

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 an exact match
- TRUE will
  is
  be an approximate match