Deductions
in job sheets 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 helpful 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
=IF(N13="",Data!BA11,IF(Data!BA11>0,Data!BA11-VLOOKUP(G13.
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"
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,
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.
Go to "Formulas" in the menu bar and select "Define 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 are multiple named fields (generally, I used the HEADING from the Group options so that I know which area the deduction is relevant to.
Once you have added the named field
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 often as you require with the other 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 formulas or modifications to a job sheet, it is easier to use a job sheet with data.
By data, I mean a job sheet that has been printed out from an order; the 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 use the "Width", and Column "M" will be the 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"
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 an exact match
TRUE will be an approximate match
Option 2
If you require multiple values, you can use the concatenate function to have several answers provide a value
=Data!BB11-VLOOKUP(CONCATENATE(H13&"|"&L13&"|"&N13),FitTouchingFloorDeduc,2,FALSE),0))
main table
