table set up for mail merge
Hello,
Bottom Line Up Front:
I am attempting to set up an Excel sheet to use as the Merge Fields for a mail merge.
Background:
I have over 2300 supervisors at varying levels of experience that need the appropriate level of training. I pull a report from our data bank and save as an Excel sheet. From this data, i am trying to mine the length of time the supervisor has been in their position – Based on their start date and send the notifications accordingly.
First, I added a column for length of years and used this formula =DAYS(NOW(),F2)/365 and moved the decimal down to 2, e.g., “0.12”, to help me see if they are in the ranges i need; e.g., less than 1 year, between first and third year, and then the three and over years of experience. – this formula works fine.
Next, to initiate a mail merge to send mass invitations to the different training levels required by their experience, i added another column as “Subject” and used this formula
=IF(H2<1,”Priority”,IF(H2=1.1>=2.9,”CatchUp”,IF(H2>3,”Refresher”)))
the “Priority” and “CatchUp” work as i have hoped; however, the 1st Challenge – the “Refresher” does not ( i get no “Refresher” results). To further this challenge, i later discovered some people are on probation and some are not (because they previously held a supervisory position and did not need to redo it, yet they are in a new supervisory position) – so challenge #2 is to fix the above subject formula to include if there is a date in the Probation column to make this a “Priority” // also, if it does not have a date, to make this a “Catch Up”. #3 once i get these formulas fixed, is it possible to change the formatting to highlight Priority as Red, CatchUp as Orange, and Refresher as light green?
The next phase of the mail merge is to establish a body column – which i have not even attempted yet… Nevertheless, i am hoping that as the Subject Column is corrected that a NEW column of “Body” will reflect accordingly with:
“Priority = You are identified in HR Smart as a New Supervisor still on probation; Please, see the attachment to register. ” for
“CatchUp = You are identified in HR Smart as a Supervisor within your first and second years of experience; Please, see the attachment to register.” and finally, for
“Refresher = You are identified in HR Smart as a Seasoned Supervisor; Please, see the attachment to register.”
Can you help me fix/establish the formulas Please, so that i can then continue building the Mail Merge portion?
here is a screen shot to help you visualize this challenge:
Thank you in advance for even offering to help me.
v/r,
dave
Hello, Bottom Line Up Front:I am attempting to set up an Excel sheet to use as the Merge Fields for a mail merge. Background:I have over 2300 supervisors at varying levels of experience that need the appropriate level of training. I pull a report from our data bank and save as an Excel sheet. From this data, i am trying to mine the length of time the supervisor has been in their position – Based on their start date and send the notifications accordingly. First, I added a column for length of years and used this formula =DAYS(NOW(),F2)/365 and moved the decimal down to 2, e.g., “0.12”, to help me see if they are in the ranges i need; e.g., less than 1 year, between first and third year, and then the three and over years of experience. – this formula works fine. Next, to initiate a mail merge to send mass invitations to the different training levels required by their experience, i added another column as “Subject” and used this formula =IF(H2<1,”Priority”,IF(H2=1.1>=2.9,”CatchUp”,IF(H2>3,”Refresher”)))the “Priority” and “CatchUp” work as i have hoped; however, the 1st Challenge – the “Refresher” does not ( i get no “Refresher” results). To further this challenge, i later discovered some people are on probation and some are not (because they previously held a supervisory position and did not need to redo it, yet they are in a new supervisory position) – so challenge #2 is to fix the above subject formula to include if there is a date in the Probation column to make this a “Priority” // also, if it does not have a date, to make this a “Catch Up”. #3 once i get these formulas fixed, is it possible to change the formatting to highlight Priority as Red, CatchUp as Orange, and Refresher as light green? The next phase of the mail merge is to establish a body column – which i have not even attempted yet… Nevertheless, i am hoping that as the Subject Column is corrected that a NEW column of “Body” will reflect accordingly with:”Priority = You are identified in HR Smart as a New Supervisor still on probation; Please, see the attachment to register. ” for”CatchUp = You are identified in HR Smart as a Supervisor within your first and second years of experience; Please, see the attachment to register.” and finally, for”Refresher = You are identified in HR Smart as a Seasoned Supervisor; Please, see the attachment to register.” Can you help me fix/establish the formulas Please, so that i can then continue building the Mail Merge portion?here is a screen shot to help you visualize this challenge: Thank you in advance for even offering to help me.v/r,dave Read More