Deductions are 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.Any deduction you need can be added .
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.
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
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
=IF(N13="",Data!BA11,IF(Data!BA11>0,Data!BA11-VLOOKUP(G13&"|"&N13,Fit,2,FALSE),0))
