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).
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 via the form is as following:
emailof respondent (collected automatically).datetimeof response (collected automatically).widget_idof the rated widget (selected by respondent from a dropdown).ratingof the widget determined by the respondent (numerical entry within a set range, e.g. 1 ... 100 )
These data are saved into an Excel per default settings of Microsoft Forms.
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 (the actual discussion and agreement happens offline, and after the invitation, Excel automation is not needed), 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
| A | B | C |
|---|---|---|
| name | emailed | |
[email protected] |
Jack | 0 |
[email protected] |
Mack | 0 |
[email protected] |
Iris B | 0 |
(column C is an email sent flag)
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
| A | B | C | D | E | F | G | H | I | J |
|---|---|---|---|---|---|---|---|---|---|
| widget_id | email1 | rating1 | email2 | rating2 | difference | email1thanked | email2thanked | actionsent | average |
| 990235 | [email protected] |
[email protected] |
0 | 0 | 0 | ||||
| 990236 | [email protected] |
[email protected] |
0 | 0 | 0 | ||||
| 990231 | [email protected] |
[email protected] |
0 | 0 | 0 | ||||
| 990197 | [email protected] |
[email protected] |
0 | 0 | 0 | ||||
| 990003 | [email protected] |
[email protected] |
0 | 0 | 0 |
(columns G, H, I are email sent flags)
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
| A | B |
|---|---|
| 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
| A | B | C | D | E |
|---|---|---|---|---|
| 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>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>Kind regards,</p><p> </p><p>Aquaman</p> |
Finally, I have added a sheet reminder with 1 column containing a list of reminder dates:
| A |
|---|
| date |
| 2023-06-24 |
| 2023-06-30 |
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 testers sheet's column C (emailed), 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).
If the column F value is within the average threshold, calculate Column J as (column F / 2)
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).
5 At 8am on the date in reminder sheet (if one of the dates listed is happens on today), 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