I am using Office 365 / Microsoft Forms to collect information from users within my organization (i.e. sign in is required to fill up the form).
Let's say the goal is quality control, and a bunch of widgets are assigned to colleagues to test and rate. A specific widget (identified by `widget_id`) is always assigned to exactly 2 testers. Each tester can be assigned any number of widgets. Once a tester has rated a widget, they input their rating via the Microsoft Forms form mentioned above.
The info collected is as following:
`email` of respondent (collected automatically).
`datetime` of response (collected automatically).
`widget_id` of the rated widget (selected by respondent from a dropdown).
`rating` of the widget determined by the respondent (numerical entry within a set range, e.g. 1 ... 5, or 1 ... 100 etc)
The rules are that if the ratings of the two testers are close enough, these can just be averaged to determine the rating of the widget, if the ratings are moderately apart, then the testers are invited to discuss their ratings with each other and agree on a common rating, and if the ratings are quite far apart, then the widget is sent to a third (senior) tester, whose rating dominates, irrespective of what the first two testers rated the widget as. (the assignment to the third tester is done manually - no Excel work is needed for that)
On the forms response Excel workbook, I have added a sheet `testers` with 2 columns depicting a list of testers
A tester email
B tester name
| email | name |
|------------------|--------|
| jack@company.com | Jack |
| mack@company.com | Mack |
| iris@company.com | Iris B |
(we'll add a column for email sent flag later)
I have also added a sheet `allocations` with 5 columns which has information on which testers have been allocated to each widget:
A widget ID
B email of tester 1 assigned to the widget
C widget rating by tester 1
D email of tester 2 assigned to the widget
E widget rating by tester 2
F Differences in the ratings
| widget_id | email1 | rating1 | email2 | rating2 | difference |
|-----------|--------|---------|--------|---------|------------|
| | | | | | |
(we'll add a column for email sent flag later)
Obviously, the columns B and D are set to do data validation from column A of `testers` sheet.
I have also added a sheet `thresholds` which has information on the action thresholds for different levels of differences in rating:
A what action should be taken
B threshold of the difference in ratings to trigger this action
| action | threshold |
|---------|-----------|
| average | 5 |
| discuss | 10 |
The above values mean that I would like the ratings to be averaged if the difference between the two testers is less than (or equal to) 5, ask the testers to discuss and agree on a rating if their ratings are more than 5 but less than (or equal to) 10 apart), and assign the testing to the senior tester if the difference is more than 10 apart.
Next, I have added a sheet `text` with 5 columns containing email texts:
A name of email (so that we can refer to each text by name -not to be sent)
B description- of email (so that we know the purpose of the email - not to be sent)
C subject of email
D body of email
E the email signature text
| name | description | subject | body | signoff |
|-----------|--------------------------------------------------------------------------------------|--------------------------------------|--------------------------------------------------------------------------------------------------------------------------|-----------------------------------------------|
| allocated | initial email informing testers which widgets have been allocated to them | List of your widgets | `<p>Thank you for agreeing to test the widgets.</p><p>The IDs of the widgets assigned to you are as following:</p>` | `<p>Best regards,</p><p> </p><p>The Batman</p>` |
| thanks | thank you email after each rating submission by tester | Thank you for submitting your rating | `<p>Thank you for submitting your rating. </p><p>I'll get back to you with further actions once I have heard from the other tester.</p>` |`<p> Best regards,</p><p> </p><p>Robin</p>` |
| average | the email sent to the 2 testers of a widget when their rating difference is low | All done! | `<p>Great stuff! You and the other tester were really close, so I'll just average your ratings. </p><p>Nothing more to do. </p>` | `<p>Cheerio,</p><p> </p><p>Superman</p>` |
| discuss | the email sent to the 2 testers of a widget when their rating difference is moderate | Can you talk? | `<p>You and the other tester are a bit apart. </p><p>Can you please have a discussion and see if you can come to an agreement? </p>` | `<p>Bye now,</p><p> </p><p>Wonderwoman</p>` |
| third | the email sent to the 2 testers of a widget when their rating difference is large | Gonna escalate this | `<p>Uh, oh! You and the other tester are quite some way from each other.</p><p> I'll have to refer this to a third tester. </p>` | `<p><p>Toodloo,</p><p> </p><p>Catwoman</p>` |
| reminder | the email sent to the testers whose ratings have not been received by reminder dates | C'mon now | `<p>Seems you are busy.</p><p> The IDs of the widgets for which ratings are awaited from you are as following:</p>` | `<p><p>Kind regards,</p><p> </p><p>Aquaman</p>` |
Finally, I have added a sheet `reminder` with 1 column containing a list of reminder dates:
What I'd like (forms responses workbook) Excel to do is:
1. When I click a button (let's say it is labeled "Send") on `allocations` Sheet, it should do a mail merge, and email all testers (listed on `testers` sheet) who have been allocated at least one widget on the `allocations` sheet and who have not been already emailed about it, per column C (`emailed`) of `testers` sheet, which is a boolean flag (default value 0) to indicate whether the tester has already been emailed with a list of allocations. After sending the email to a tester, set the column C flag (`emailed`) to 1. The email to be sent is the email named `allocated` on the `text` sheet. The email "to" field would be the tester's email. The subject would be as per `subject` column. The way to assemble the email body is: text "Dear "+`tester name`+`body`+list of widget IDs from `allocations` sheet for which the tester email is listed as `email1` or `email2` (comma separated))+`signoff`
2. As various testers submit their forms, Excel should send them the `thanks` emails. The email "to" field would be the tester's email. The subject would be as per `subject` column, prefixed with widget ID. The way to assemble the email body is: text "Dear "+`tester name`+`body`+`signoff`
Update boolean flag (default value 0) to 1 in column G (email1thanked) or H (email2thanked) as appropriate.
3. As various testers submit their forms, Excel should populate columns C/E of `allocations` sheet, depending on the widget id and tester email. Let's say the Forms submissions are coming to "Sheet 1". If columns C and E both have numerical values, then column F should be calculated as ABS(C-E).
4. Once `allocations` sheet has a numerical value for column F (i.e. ratings difference) for a widget ID, then based on the value in column F for that widget ID in accordance to the thresholds, Excel should send `average`, `discuss` or `third` email to both testers of that widget.
The email "to" field would be the tester's email. The subject would be as per `subject` column, prefixed with widget ID.
The way to assemble the body for `average` and `third` emails is: text "Dear "+`tester name`+`body`+`signoff`
The way to assemble the body for `discuss` emails is: text "Dear "+`tester name`+`body`+the name & email address of the other tester (i.e. if sending mail to tester 1, provide name & email of tester 2, if sending mail to tester 2, provide name and email of tester 1)+`signoff`
Update boolean flag (default value 0) to 1 in column I (actionsent) as appropriate.
5. At 8am on the date in `reminder` sheet, send reminder emails to all testers for rating values have not been received.
The email "to" field would be the tester's email. The subject would be as per `subject` column. The way to assemble the email body is: text "Dear "+`tester name`+`body`+list of widget IDs from `allocations` sheet for which the tester email is listed as `email1` or `email2` (comma separated)) AND the respective rating is empty+`signoff`
Let's say the goal is quality control, and a bunch of widgets are assigned to colleagues to test and rate. A specific widget (identified by `widget_id`) is always assigned to exactly 2 testers. Each tester can be assigned any number of widgets. Once a tester has rated a widget, they input their rating via the Microsoft Forms form mentioned above.
The info collected is as following:
`email` of respondent (collected automatically).
`datetime` of response (collected automatically).
`widget_id` of the rated widget (selected by respondent from a dropdown).
`rating` of the widget determined by the respondent (numerical entry within a set range, e.g. 1 ... 5, or 1 ... 100 etc)
The rules are that if the ratings of the two testers are close enough, these can just be averaged to determine the rating of the widget, if the ratings are moderately apart, then the testers are invited to discuss their ratings with each other and agree on a common rating, and if the ratings are quite far apart, then the widget is sent to a third (senior) tester, whose rating dominates, irrespective of what the first two testers rated the widget as. (the assignment to the third tester is done manually - no Excel work is needed for that)
On the forms response Excel workbook, I have added a sheet `testers` with 2 columns depicting a list of testers
A tester email
B tester name
| email | name |
|------------------|--------|
| jack@company.com | Jack |
| mack@company.com | Mack |
| iris@company.com | Iris B |
(we'll add a column for email sent flag later)
I have also added a sheet `allocations` with 5 columns which has information on which testers have been allocated to each widget:
A widget ID
B email of tester 1 assigned to the widget
C widget rating by tester 1
D email of tester 2 assigned to the widget
E widget rating by tester 2
F Differences in the ratings
| widget_id | email1 | rating1 | email2 | rating2 | difference |
|-----------|--------|---------|--------|---------|------------|
| | | | | | |
(we'll add a column for email sent flag later)
Obviously, the columns B and D are set to do data validation from column A of `testers` sheet.
I have also added a sheet `thresholds` which has information on the action thresholds for different levels of differences in rating:
A what action should be taken
B threshold of the difference in ratings to trigger this action
| action | threshold |
|---------|-----------|
| average | 5 |
| discuss | 10 |
The above values mean that I would like the ratings to be averaged if the difference between the two testers is less than (or equal to) 5, ask the testers to discuss and agree on a rating if their ratings are more than 5 but less than (or equal to) 10 apart), and assign the testing to the senior tester if the difference is more than 10 apart.
Next, I have added a sheet `text` with 5 columns containing email texts:
A name of email (so that we can refer to each text by name -not to be sent)
B description- of email (so that we know the purpose of the email - not to be sent)
C subject of email
D body of email
E the email signature text
| name | description | subject | body | signoff |
|-----------|--------------------------------------------------------------------------------------|--------------------------------------|--------------------------------------------------------------------------------------------------------------------------|-----------------------------------------------|
| allocated | initial email informing testers which widgets have been allocated to them | List of your widgets | `<p>Thank you for agreeing to test the widgets.</p><p>The IDs of the widgets assigned to you are as following:</p>` | `<p>Best regards,</p><p> </p><p>The Batman</p>` |
| thanks | thank you email after each rating submission by tester | Thank you for submitting your rating | `<p>Thank you for submitting your rating. </p><p>I'll get back to you with further actions once I have heard from the other tester.</p>` |`<p> Best regards,</p><p> </p><p>Robin</p>` |
| average | the email sent to the 2 testers of a widget when their rating difference is low | All done! | `<p>Great stuff! You and the other tester were really close, so I'll just average your ratings. </p><p>Nothing more to do. </p>` | `<p>Cheerio,</p><p> </p><p>Superman</p>` |
| discuss | the email sent to the 2 testers of a widget when their rating difference is moderate | Can you talk? | `<p>You and the other tester are a bit apart. </p><p>Can you please have a discussion and see if you can come to an agreement? </p>` | `<p>Bye now,</p><p> </p><p>Wonderwoman</p>` |
| third | the email sent to the 2 testers of a widget when their rating difference is large | Gonna escalate this | `<p>Uh, oh! You and the other tester are quite some way from each other.</p><p> I'll have to refer this to a third tester. </p>` | `<p><p>Toodloo,</p><p> </p><p>Catwoman</p>` |
| reminder | the email sent to the testers whose ratings have not been received by reminder dates | C'mon now | `<p>Seems you are busy.</p><p> The IDs of the widgets for which ratings are awaited from you are as following:</p>` | `<p><p>Kind regards,</p><p> </p><p>Aquaman</p>` |
Finally, I have added a sheet `reminder` with 1 column containing a list of reminder dates:
What I'd like (forms responses workbook) Excel to do is:
1. When I click a button (let's say it is labeled "Send") on `allocations` Sheet, it should do a mail merge, and email all testers (listed on `testers` sheet) who have been allocated at least one widget on the `allocations` sheet and who have not been already emailed about it, per column C (`emailed`) of `testers` sheet, which is a boolean flag (default value 0) to indicate whether the tester has already been emailed with a list of allocations. After sending the email to a tester, set the column C flag (`emailed`) to 1. The email to be sent is the email named `allocated` on the `text` sheet. The email "to" field would be the tester's email. The subject would be as per `subject` column. The way to assemble the email body is: text "Dear "+`tester name`+`body`+list of widget IDs from `allocations` sheet for which the tester email is listed as `email1` or `email2` (comma separated))+`signoff`
2. As various testers submit their forms, Excel should send them the `thanks` emails. The email "to" field would be the tester's email. The subject would be as per `subject` column, prefixed with widget ID. The way to assemble the email body is: text "Dear "+`tester name`+`body`+`signoff`
Update boolean flag (default value 0) to 1 in column G (email1thanked) or H (email2thanked) as appropriate.
3. As various testers submit their forms, Excel should populate columns C/E of `allocations` sheet, depending on the widget id and tester email. Let's say the Forms submissions are coming to "Sheet 1". If columns C and E both have numerical values, then column F should be calculated as ABS(C-E).
4. Once `allocations` sheet has a numerical value for column F (i.e. ratings difference) for a widget ID, then based on the value in column F for that widget ID in accordance to the thresholds, Excel should send `average`, `discuss` or `third` email to both testers of that widget.
The email "to" field would be the tester's email. The subject would be as per `subject` column, prefixed with widget ID.
The way to assemble the body for `average` and `third` emails is: text "Dear "+`tester name`+`body`+`signoff`
The way to assemble the body for `discuss` emails is: text "Dear "+`tester name`+`body`+the name & email address of the other tester (i.e. if sending mail to tester 1, provide name & email of tester 2, if sending mail to tester 2, provide name and email of tester 1)+`signoff`
Update boolean flag (default value 0) to 1 in column I (actionsent) as appropriate.
5. At 8am on the date in `reminder` sheet, send reminder emails to all testers for rating values have not been received.
The email "to" field would be the tester's email. The subject would be as per `subject` column. The way to assemble the email body is: text "Dear "+`tester name`+`body`+list of widget IDs from `allocations` sheet for which the tester email is listed as `email1` or `email2` (comma separated)) AND the respective rating is empty+`signoff`
