Answered

Use a form to enter data into multiple excel sheets within the same workbook

(0) Share

Report

Posted on by Doug-632

I am managing data entry of what amounts to a survey. Each participant response is essentially a ballot that would add 1 new line of data for multiple sheets. I'd like to create some sort of form or flow, so I can record one full participant response, and have the answer populate one line of each sheet.

Right now I have my spreadsheet formatted as a table, so if I use the Form function on the ribbon I can enter a full line of data within a given sheet. I'd like to be able to record this data all at once for each sheet, if that makes sense.

I'll try to outline an example:

Participant 1 ballot

Question 1

Part A- 1
Part B- 2
Part C- 3

Question 2

Part A- 7
Part B- 8
Part C- 9

The form/flow I'd like to create would allow me to record this data in this order, then the excel workbook would be populated as follows.

In sheet 1, a new line entry with values 1, 2, 3

In sheet 2, a new line entry with values 7, 8, 9

I have to repeat this process many times, for many questions, over many sheets, but adding form entries sheet-by-sheet won't really save me any time. If I can basically input the data in the same way it's recorded in the survey, it would make life much easier.

Apologies if this is unclear, I'm not familiar with Forms, Flow, or Power Automate but I'd like to learn.

Thank you!

Categories:

Power Automate Desktop

I have the same question (0)

All responses (2)

Answers (1)

Sort by

Verified answer

MichaelAnnis 5,727 Moderator on at

Like (1)

Report
Copy link

Link copied!

Check out the solution provided here and modify the breakouts and logic as needed:

https://powerusers.microsoft.com/t5/Power-Automate-Desktop/Crating-List/m-p/2718425/emcs_t/S2h8ZW1haWx8Ym9hcmRfc3Vic2NyaXB0aW9ufExWQkM4T0xOU0k4Mk1BfDI3MTg0MjV8U1VCU0NSSVBUSU9OU3xoSw#M39683

instead of breaking at "----------------------------", you want to break each section at "Question" and then find "Part A-" and take whatever is remaining to the end. Might want to Trim after to get rid of beginning and ending spaces. As for flow logic I would process each response using a mapping table to tie each questions to a sheet:

Launch Excel

Get responses (I don't know if this is text files or what), but getting them to a list variable like GetFiles would is the preferred method.

For each response

Use logic to break out sections by "Questions"

For each question

Use logic to break out answer

Attach to proper sheet for this question

Find first available row

Enter answer

End for each

End for each

Good luck!

Was this reply helpful? Yes No
Doug-632 5 on at

Like (0)

Report
Copy link

Link copied!

Thanks so much for this response, it's very helpful.

I can clarify your question about the format of the responses. Basically a single response is a physical sheet of paper that is filled out by hand. It's a sensory evaluation of a product that we have select members of the team fill out, and since it's done in a lab it doesn't make sense for everyone to be recording it into a laptop. It's archaic and takes forever, which is part of why I'm trying to automate the entry process as much as possible.

So basically, for each response, data entry involves manually recording each of the answers to each question into a table. I was picturing using a Microsoft Form as a way to make data entry easier, basically generate some kind of interface that would prompt the entry for each answer in order.

Then on the backend I would take the entered data and apply your solution to it, basically have it broken down by Question and distributed into the proper sheet. Thanks!

Was this reply helpful? Yes No