In this episode we will cover the remaining steps to automate contract generating procedures; if you this seems advanced to you, go through episode 1 and follow the step by step tutorial. In this episode we will continue developing on the same basis we established; at this point your connected word and excel should files should generate the following variables based on excel input i.e (name, position, department, salary, reporting line…etc) and other variables in the contract.

The variables established in excel sheet is sort of constant variables based on single input that changes based on data input change; however, since in every organization you usually have different levels with different benefits or in some cases you may have additional conditions you might need to add or remove based on each contract condition.

Since input is driven from excel, most of the work will be executed in excel and result will be yield in word with a tweak to adjust white space in most cases probably; you will require some knowledge on how to write formulas in excel; we will use the following functions:
- Vlookup (Data Matching)
- If (logical)

Take a final look at your contract condition and ensure you determine the area you will need to distinguish for these operations to work correctly. Most of benefits are associated with articles in employment contract if you have the same rules and benefits applicable for everyone you do need to make a rule it will unnecessary complicate the document for example.

(Meals) if all your staff are taking meals in a staff area, you don’t need to include, however (air ticket) which varies in duration and frequency you would consider include; it will all makes sense when assembled together.

Step 1: Create a new worksheet in the same workbook name it Benefits (or any other name of your preference).

Step 2: List your document by level or by grade; the following example will cover the 3 different cases you may encounter which applicable to all other; the way data sorted in excel is a key in order for this operation to work perfectly every time.

Take a close look at above figure, there are 8 cases for 3 different levels and 3 different benefits that require a complete inclusion of the whole contract article to be included in the merge field, now let’s set the vlookup formula (if you know already know how vlookup works skip this part)

What vlookup does? It looks at a selected value and look for it in a range and if it finds (absolute or approximate match) it return a preset matching column value Let’s talk a bit about this formula

If written correctly, you should be able to pull matching result of the value searched for as follows:

Now you need to copy paste and paste the formula in the column (vertically) to produce the following result

For the rest of the columns, consequentially the formula need to be adjusted to look for the exact match as follows only a change in reading column will change:

=VLOOKUP(A2 , Benefits!$A$1:$D$4, 3 ,FALSE)

If done correctly, you should be able to get each value for the corresponding level or grade as shown in below diagram.

Now you notice when values are not present you are faced with a situation where a ZERO is displayed for empty values, which stall automation and may require manual adjustment! Well not exactly, a simple IF formula will solve this issue and give a clean empty cell (if you know conditional if formulas you may skip this part)

=IF(VLOOKUP(A2,Benefits!$A$1:$D$4,4,FALSE) = 0,"",VLOOKUP(A2,Benefits!$A$1:$D$4,4,FALSE))

The if formula evaluates results if ZERO it will fill the cell with “” which is empty or it will leave it as is, this process to be replicated for all contract condition and this is how you can produce your contract with variables in conditions and terms in less than three mins!