Search code examples
excelexcel-formulaoffice365microsoft-forms

Dynamically update a sheet based on values received in another sheet (within same workbook) from a form

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:

  • 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
email date widget_id rating
[email protected] 2023-04-23 990236 65
[email protected] 2023-04-23 990236 75
[email protected] 2023-04-23 990003 50
[email protected] 2023-04-24 990235 45

I have another sheet allocations with 6 relevant 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
  • 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 [email protected] 0 [email protected] 0 0
990236 [email protected] 0 [email protected] 0 0
990231 [email protected] 0 [email protected] 0 0
990197 [email protected] 0 [email protected] 0 0
990003 [email protected] 0 [email protected] 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 email1then copy theratingfrom this row inallocationssheet torating1inallocationssheet, if it matchesemail2then copy theratingtorating2`.

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 [email protected] 0 45 [email protected] 0 0
990236 [email protected] 0 75 [email protected] 0 65 0
990231 [email protected] 0 [email protected] 0 0
990197 [email protected] 0 [email protected] 0 0
990003 [email protected] 0 [email protected] 0 50 0
Solution

  • To get the data till the last row and produce the desired outcome, just like i had posted in comments, please refer the following formulas.

    enter image description here

    • Formula used in cell D2

    =LET(
     x,MATCH(7^89,A:A),
     y,A2:INDEX(A:A,x),
     z,B2:INDEX(B:B,x),
     XLOOKUP(y&"|"&z,'Form 1'!C:C&"|"&'Form 1'!A:A,'Form 1'!D:D,""))

    • Formula used in cell G2

    =LET(
     x,MATCH(7^89,A:A),
     y,A2:INDEX(A:A,x),
     z,E2:INDEX(E:E,x),
     XLOOKUP(y&"|"&z,'Form 1'!C:C&"|"&'Form 1'!A:A,'Form 1'!D:D,""))

    To get data till last row, i had posted the other alternatives in your last post: Conditionally calculate difference between two columns on a sheet

    Workbook