I am using Office 365. In order to collect info from forms in a live Excel sheet, I created a new Excel Online workbook/ file (basically within the browser, logged into Office 365, went to Excel and "Create New" which created an Excel file in OneDrive) and did `Insert > Forms` to create a new Microsoft Forms form.
The Microsoft Forms form is intended to collect information from users within my organization (i.e. sign in is required to fill up the form). The information collected is widget ratings.
Context:
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:
So, in my Excel workbook's forms sheet, it appears as:
Let's say the Forms submissions sheet is called `Form 1`.
I have another sheet `allocations` with 6 relevant columns which has information on which two testers have been allocated to test/rate each widget:
What I want to happen
As various testers submit their forms, Excel should populate column D or G (as appropriate) of `allocations` sheet, depending on the widget id and tester email. In other words, I want Excel to copy over the ratings from the `Form 1` sheet to the appropriate place in the more understandable `allocations` sheet.
In other words,
the moment, the data row appears in `Form 1` sheet,
Excel should look for the `widget_id` from this row in `allocations` sheet.
Further, Excel should check whether the `email` from this row in `Form 1` sheet matches `email1` in `allocations` sheet or `email2` in `allocations` sheet in the row in which this `widget_id is found in `allocations`.
If it matches `email1` then copy the `rating` from this row in `allocations` sheet to `rating1` in `allocations` sheet, if it matches `email2` then copy the `rating` to `rating2`.
So, in our example `allocations` sheet would look like
The Microsoft Forms form is intended to collect information from users within my organization (i.e. sign in is required to fill up the form). The information collected is widget ratings.
Context:
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:
- `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 ... 100 )
So, in my Excel workbook's forms sheet, it appears as:
Let's say the Forms submissions sheet is called `Form 1`.
| A | B | C | D |
|---|---|---|---|
| date | widget_id | rating | |
| jack@company.com | 2023-04-23 | 990236 | 65 |
| mack@company.com | 2023-04-23 | 990236 | 75 |
| iris@company.com | 2023-04-23 | 990003 | 50 |
| jack@company.com | 2023-04-24 | 990235 | 45 |
I have another sheet `allocations` with 6 relevant columns which has information on which two testers have been allocated to test/rate each widget:
- A widget ID
- B email of tester 1 assigned to the widget
- D widget rating by tester 1
- E email of tester 2 assigned to the widget
- G widget rating by tester 2
- Columns C, F, and H-J are irrelevant for this issue
| A | B | C | D | E | F | G | H | I | J |
|---|---|---|---|---|---|---|---|---|---|
| widget_id | email1 | email1thanked | rating1 | email2 | email2thanked | rating2 | actionsent | difference | average |
| 990235 | jack@company.com | 0 | mack@company.com | 0 | 0 | ||||
| 990236 | mack@company.com | 0 | jack@company.com | 0 | 0 | ||||
| 990231 | jack@company.com | 0 | iris@company.com | 0 | 0 | ||||
| 990197 | iris@company.com | 0 | mack@company.com | 0 | 0 | ||||
| 990003 | mack@company.com | 0 | iris@company.com | 0 | 0 |
What I want to happen
As various testers submit their forms, Excel should populate column D or G (as appropriate) of `allocations` sheet, depending on the widget id and tester email. In other words, I want Excel to copy over the ratings from the `Form 1` sheet to the appropriate place in the more understandable `allocations` sheet.
In other words,
the moment, the data row appears in `Form 1` sheet,
Excel should look for the `widget_id` from this row in `allocations` sheet.
Further, Excel should check whether the `email` from this row in `Form 1` sheet matches `email1` in `allocations` sheet or `email2` in `allocations` sheet in the row in which this `widget_id is found in `allocations`.
If it matches `email1` then copy the `rating` from this row in `allocations` sheet to `rating1` in `allocations` sheet, if it matches `email2` then copy the `rating` to `rating2`.
So, in our example `allocations` sheet would look like
| A | B | C | D | E | F | G | H | I | J |
|---|---|---|---|---|---|---|---|---|---|
| widget_id | email1 | email1thanked | rating1 | email2 | email2thanked | rating2 | actionsent | difference | average |
| 990235 | jack@company.com | 0 | 45 | mack@company.com | 0 | 0 | |||
| 990236 | mack@company.com | 0 | 75 | jack@company.com | 0 | 65 | 0 | ||
| 990231 | jack@company.com | 0 | iris@company.com | 0 | 0 | ||||
| 990197 | iris@company.com | 0 | mack@company.com | 0 | 0 | ||||
| 990003 | mack@company.com | 0 | iris@company.com | 0 | 50 | 0 |
