Using forms to populate an existing Excel file

[Laurens2207]

Hi everyone,

I have a question related to forms. I work in Teams on a excel file (name: File A) that I need my team to populate with data. Now File A is quite big (and for outsiders a bit messy :)) and I do not want my team members to go into File A.

Still, I need their help on gathering information. I thought of using Microsoft Forms to help me. I would set up a questionnaire, let everyone fill it out and I have my data in a new excel file (name: File B). Now the thing is, data would come in every now and then, meaning I regularly have to go back to File B and copy my data in to File A.

Is there a way I can let the form populate File A directly and choose where the data would land manually? And if so, how?
If not, would any of you have a suggestion how to best tackle my problem?

Thanks for your help!

[#2]

@Laurens2207 the form can't populate File A directly but you can grab each form response with a flow in Power Automate and add the answers to each question to the appropriate column in a table in File A on OneDrive.

Rob
Los Gallardos
Microsoft Power Automate Community Super User

[#3]

@RobElliott 

I'm having issues getting a form to add the row. I have one working from 3 years ago. I used the same process and it just doesn't work. What has changed?

[#4]

Nothings changed with that as far as I know. If you post a screenshot of the flow we could take a look.

[#5]

@RobElliott 

I'm not seeing the choices to link

[#6]

@tjzarra you don't want that apply to each: it is an error in the template that Microsoft have never fixed and it causes problems. Delete it. Immediately after your when a new response is submitted trigger you need the get response details action then everything should be fine after that.

Rob
Los Gallardos
Microsoft Power Automate Community Super User

[#7]

@Laurens2207 

I was looking into this same issue and found an alternative solution. Forms for Excel.

In the appropriate Sharepoint (or OneDrive) folder online create a new file called Forms for Excel (using the New button). It will be tied to the created Form and live updated based on the form submissions. Unfortunately, I believe Forms for Excel can only be added to a new Excel file but you can copy over any old info from your current Excel file.

More info about creating Forms for Excel on this video. They talk about Microsoft Forms vs Forms for Excel. The Forms for Excel part starts at 3:00 if you want to skip ahead. This is not my video, just the simplest most to the point one I could find quickly.

How to Create a Microsoft Form with Live Responses in Excel - YouTube

[#8]

@Laurens2207 

In teams:

1. I would personal create a teams channel for yourself called Forms data or something like this, you can add people to this as admin if there is someone else assisting with the data ...

2. in that channel add a tab for forms

3. this gives you option to add an existing form ( I would create from scratch tbh thinking of all the stuff you may require on the form) (do not be afraid of testing the forms branching options as this can be really useful tool)

4. this then stores the forms excel in your teams channel files tab.

5. this excel you can edit loads on that will not show on the form, even make dashboards etc. 

6. this enables the form to be edited in that teams channel if you require it too... each edit will add an extra column on excel to end but you can drag the excel columns around without affecting your form

You can add the data from your other workbook to this workbook, as a one off then it will be there but not show on form  

if you was to ever leave business with it been in a teams channel if it had a 2nd and 3rd admin they would still be able to keep this form current in the company.