Forum Discussion
Populating an existing Excel spreadsheet with results from a MS Forms quiz/form
This one is a big ask and I'm almost certain that it isn't possible but I need it to be.
I've created a quiz in MS Forms and I can obviously open the results up in an MS Excel spreadsheet when I've had responses. The problem is, I want to somehow link the answers from the quiz to an existing MS Excel spreadsheet. There must surely be a way even if it means linking each answer from each question to each specific cell.
This is the spreadsheet I want to be able to link it to...
Along the top are the 12 questions. I'd love it if the points acquired (either 1 or 0) would populate the corresponding boxes. This seems like it should be doable.
If there is any other software that could link up with excel in this way, I'm open for suggestions.
Thanks in advance.
In any case you need to map people names and questions text in report sheet and in forms sheet if they are not exactly the same. Most probably not. That could be done automatically if you have some logic behind.
With that we may use lookup functions (XLOOKUP, INDEX/MATCH, etc) in report sheet to pickup the answers from the form sheet.
The rest depends on details. Is form returns answers on automatically updated Excel file on SharePoint/OneDrive; or you download that file from the Forms. Does the person could fill the form multiple times or only once. Is the same form for series of answers (e.g. this quarter, next quarter, etc) or the form is used only once for the concrete assessment.
Preparations could be done in separate sheet of the report file, for example using Power Query to get data from the form file.
Afraid can't be more concrete based on information provided.
