Creating a survey & collecting feedback for a list of submissions

eric.wong.compass · 02/16/23

I've created an intake form and sheet to collect project request submissions, and now need to create a way for the project committee (8 members) to score each submission using a simple 4 value Impact/Effort score that I'd like to present in a Survey Form so I can collect the data and consolidate for presentation on results.

Looking for input on the additional sheet structures needed to accomplish this. I initially created a 2nd sheet w/ an automation that pulls the submissions from the submissions sheet based on a check box criteria, then added additional columns for each of the 4 values I'm looking to score on, but now I'm stuck on figuring out how I would get individual survey submission results from survey participants to populate in a sheet that I can then tabulate results from.

Welcome ideas on how to setup this process!

Example of some of the main data columns in my project intake request sheet:

Request ID

Name

Email

Business problem statement

Approved to score - check box

Sheet I initially created to collect survey results via a Form:

This form populates w/ the sheet data from above based on an automation validating a check mark in "Approved to score"

Request ID

Business problem statement

Discovery Level of Effort - 1-5 scale

Time to Market - 1-5 scale

Size of Market - 1-5 scale

Connection to Strategy - 1-5 scale

Andrée Starå · 02/16/23

Hi @eric.wong.compass

I hope you're well and safe!

You could use cross-sheet formulas combined with either a VLOOKUP or INDEX/MATCH structure to connect the sheets, and when you update the source sheet, it will reflect on the destination sheet.

Another option would be to use so-called helper sheets. In short, copy the row to a helper sheet and then use my method described previously to get the values you need to another helper sheet and then copy/move the row from that sheet to the main destination sheet.

To connect them row by row, you'd use an Autonumber Column in the Source sheet and add a so-called helper column to manually add the row id on as many rows as you think you need in the Destination sheet.

Would that work/help?

I hope that helps!

Be safe, and have a fantastic week!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

David Tutwiler · 02/16/23

Probably the most straightforward way to do this would be to add your columns (4 values x 8 members who need to score) and use Dynamic view to only show each member their columns. Each member would also be able to update the entries directly from their Dynamic view and you would only need to create 1 view instead of multiple objects.

The only downside to that is that you would need access to Dynamic view, which is a premium app. In the case that you don't have that, you could also run a report and set up a filter for Current User. This would show a similar result as Dynamic View, but you would likely need to set up a report per member (8 reports) so that they only see their columns.

Hope this helps.