Mail merge with list numbers

[Chris_010101]

Hello,

I am trying to create a mail merge spreadsheet which I can use to create employment contracts. Everyone in the company is on the same employment contract and the only difference is the notice period, as follows:

Manager

During your probationary period, your employment will be subject to one week's notice from the Company or from you.

Upon successful completion of your probationary period, your employment will continue until terminated

i) by the Company by giving you three months’ notice;

ii) by you by giving the Company three months’ written notice.

Non-manager

During your probationary period, your employment will be subject to one week's notice from the Company or from you.

Upon successful completion of your probationary period, your employment will continue until terminated

by the Company;

i) if you have less than five years’ continuous service by giving one months’ notice;

ii) upon you reaching five or more years’ continuous service by giving one weeks’ notice for each completed year of service up to a maximum of twelve weeks.

by you;

i) by giving one months’ written notice.

The difference between manager/non-manager is highlighted in orange.

My mail mail merge spreadsheet uses a list drop down (manager, non-manager) in column C and I was trying to use an IF formula to return one of the above two values in column D.

New Starter Tracker.xlsx
	A	B	C	D	E	F	G	H	I	J
1	Forename	Surname	Employee type	Notice period (HIDE)	Date of Contract	Has the employee	Commencement (HIDE)	Proposed start date	Probation period (months)	Job Title
2
3
4
5
6
7
8
9
PAYE Starter

Cell Formulas
Range	Formula
G2:G9	G2	=IF(OR(ISBLANK(F2)), "", IF(F2="Not yet started", "will commence", "commenced"))

Cells with Data Validation
Cell	Allow	Criteria
F2:F9	List	Not yet started, Already started
C2:C9	List	Manager, Non-manager

But it isn't working

Thanks

 

[Chris_010101]

Bumping as no response

 

[jimrward]

Try moving one of the closing ) after F2 in your IF statement to after the double quotes
IF(OR(ISBLANK(F2), ""),IF(F2="Not yet started", "will commence", "commenced"))

 

[Chris_010101]

Try moving one of the closing ) after F2 in your IF statement to after the double quotes
IF(OR(ISBLANK(F2), ""),IF(F2="Not yet started", "will commence", "commenced"))

Hi Jim,

Thanks for your reply but the question is around adding notice periods with bulleted lists on a mail merge.

A Manager's notice period in their contract would be:

A non-manager's notice period in their contract would be:

Column C is where "manager" or "non-manager" is selected from a list and column D is where the relevant notice period above would return via formula.

I then need to be able to mail merge this result into a contract document, in the same paragraph/bullet list layout as above (italics not important).

Hope that makes sense

Cheers

 

[Chris_010101]

I have managed to do this by splitting each line of text into a new column in excel and adding a bullet point before the merge field in word.