In the course of my summer internship, I have to design a convenient way to generate contracts with the help of an Excel spreadsheet, representing a single person, containing data like name and surname, date of birth, but also quite complex calculations for part-time guidelines amongst other things. I was provided with a general template for the different types of contracts, written by the firm's lawyer. Most of the placeholders I have to dynamically fill are things like address and name, but other times I have to check whether a certain calculation in the spreadsheet amounts to a specific value, and if and only if that condition is given I have to include or exclude a clause of the contract.
Up to this point, the way I handled the situation made use of several "IF"-clauses, and the "CONCATENATE" function, which made the entire table really confusing. My question is: Is there any way to link the Excel file with a Word one, and make use of the Mailmerge-function in order to extract information from specific cells and check them against a value? This way I would be able to print certain clauses only if the condition is met. I will try to give an example:
Excel file: Contains name ("Mr. John Q. Public"), date of birth (01.01.1980), and a boolean, stating whether he is eligible for a certain discount.
Word file: Upon linking the files, Word extracts the information and puts it into the correct places. (The bold parts mark the inserted information).
Regarding the employment of Mr. John Q. Public, born 01.01.1980. We are happy to inform you about our decision to hire you as our sales manager [...]
Now to the tricky part. Let's say there's a guideline in place, stating that if you fulfill a certain condition (marked by the boolean) you're eligible for an earlier retirement age.
[...] (This part should only get printed, when a cell in the spreadsheet fulfills the specifiable condition.) Since you comply with condition xyz you are also able to retire at the age of 50! [...]
I am aware of the fact that the Mailmerge function allows for if/then/else statements (which is essentially what I am looking for), but only for a "set" of data (multiple instances of the same type), but my entire Excel file is representing a single person. Would it be possible to extract the information via reference, like ([Table]!A:0)? This way I think I could achieve the task I was given in a more elegant way. I really hope I could make myself understood. Thank you for your time and help!