Job sheet - Deductions Tab

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. 

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"

Open

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

Open

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"

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),TouchingFloorDeduc,2,FALSE)

Job sheet - How to start a Job sheet from scratch

Job sheet - Data Tab

Job sheet - The Job Sheet tab

Job sheet - Check Measure Tab

Job sheet - Barcode

Job sheet - Labels

Job sheet - Tickets

Job sheet - Components

Job sheet - BUZ 3 to BUZ Cloud