MS 365 Forms removing data?
January 19, 2022 11:03 AM Subscribe
Created an MS Form, created formulas in another sheet to analyze data collected in the form. After submitting a completed Form, the data is captured, but it deletes the copied-down formulas. How do I stop Forms/ Excel from doing this?
This is super annoying behaviour.
Form responses when submitted are logged in a bunch of columns in a single row. I created formulas in a different sheet to reference cells populated by the form.
I copy-down the formulas so row 1 references cells ?1, row 2 = references cells ?2, etc.:
1 Data....1 Formula Results
2 Data....2 Formula Results
3Blank....3 Blank Formula
4Blank....4 Blank Formula
5Blank....5 Blank Formula
If row 2 is already populated, and the next available row is 3, when submitting a form, row 3 is populated but then I get:
1 Data....1 Formula Results
2 Data....2 Formula Results
3 Data....5 Blank Formula
4Blank....6 Blank Formula
5Blank....7 Blank Formula
Where I'm expecting:
1 Data....1 Formula Results
2 Data....2 Formula Results
3 Data....3 Formula Results
4Blank....4 Blank Formula
5Blank....5 Blank Formula
I initially had my formulas in a separate sheet in the same workbook, tried putting my formulas into the same sheet as the table that Forms populates, and I've also made a copy of the separate sheet in a new workbook - and the behaviour is the same.
This is super annoying behaviour.
Form responses when submitted are logged in a bunch of columns in a single row. I created formulas in a different sheet to reference cells populated by the form.
I copy-down the formulas so row 1 references cells ?1, row 2 = references cells ?2, etc.:
1 Data....1 Formula Results
2 Data....2 Formula Results
3Blank....3 Blank Formula
4Blank....4 Blank Formula
5Blank....5 Blank Formula
If row 2 is already populated, and the next available row is 3, when submitting a form, row 3 is populated but then I get:
1 Data....1 Formula Results
2 Data....2 Formula Results
3 Data....5 Blank Formula
4Blank....6 Blank Formula
5Blank....7 Blank Formula
Where I'm expecting:
1 Data....1 Formula Results
2 Data....2 Formula Results
3 Data....3 Formula Results
4Blank....4 Blank Formula
5Blank....5 Blank Formula
I initially had my formulas in a separate sheet in the same workbook, tried putting my formulas into the same sheet as the table that Forms populates, and I've also made a copy of the separate sheet in a new workbook - and the behaviour is the same.
If you export data from Forms then a new spreadsheet is created each time, so it sounds like you've created your Form within a group, and are viewing the results in the spreadsheet that is automatically created in the Documents library of the group.
As rozcakj says, the better way to do this is to create a Power Automate workflow that automatically copies the results into an entirely separate spreadsheet. But that may not solve the issue anyway, because it sounds like the problem is with your formulas.
The most likely explanation is that your formulas are not correctly using $ signs for relative and absolute values. If you manually copy a line of data in to the spreadsheet do you get the same problem? If you do then the issue is definitely the formulas, not Forms or Excel.
posted by underclocked at 2:00 PM on January 19, 2022
As rozcakj says, the better way to do this is to create a Power Automate workflow that automatically copies the results into an entirely separate spreadsheet. But that may not solve the issue anyway, because it sounds like the problem is with your formulas.
The most likely explanation is that your formulas are not correctly using $ signs for relative and absolute values. If you manually copy a line of data in to the spreadsheet do you get the same problem? If you do then the issue is definitely the formulas, not Forms or Excel.
posted by underclocked at 2:00 PM on January 19, 2022
Response by poster: This is 365, web forms, web/ desktop excel.
Thanks for the suggestions about absolute cell references, but that wasn't it. I tried Power Automate but it wasn't playing nice with the specific way my work SharePoint is setup.
The issue has something to do with how MS Forms deposits data into excel sheets via SharePoint "assistant" or something. It seems to create new cells instead of depositing data into an existing cell so future data from Forms simply refuse be referenced before they are created. Somehow, even $x$n references in a completely separate workbook (in SharePoint) get "bumped" when a new cell is created by Forms. (workbook)'Form Responses!$B$12 in a completely separate sheet somehow gets updated to (workbook)'Form Responses!$B$13 when Forms deposits data into (seemingly) B12. So frustrating.
In the end, I used the OFFSET function with an IF else "" for as yet-to-be-conjured-into-existence cells, anchored to an existing cell in the Table that Forms adds data to, in another sheet as a "grabber" to pull data as it's entered by Forms, then another sheet to do the instantaneous calculations.
What a PITA.
posted by porpoise at 5:56 PM on January 19, 2022 [1 favorite]
Thanks for the suggestions about absolute cell references, but that wasn't it. I tried Power Automate but it wasn't playing nice with the specific way my work SharePoint is setup.
The issue has something to do with how MS Forms deposits data into excel sheets via SharePoint "assistant" or something. It seems to create new cells instead of depositing data into an existing cell so future data from Forms simply refuse be referenced before they are created. Somehow, even $x$n references in a completely separate workbook (in SharePoint) get "bumped" when a new cell is created by Forms. (workbook)'Form Responses!$B$12 in a completely separate sheet somehow gets updated to (workbook)'Form Responses!$B$13 when Forms deposits data into (seemingly) B12. So frustrating.
In the end, I used the OFFSET function with an IF else "" for as yet-to-be-conjured-into-existence cells, anchored to an existing cell in the Table that Forms adds data to, in another sheet as a "grabber" to pull data as it's entered by Forms, then another sheet to do the instantaneous calculations.
What a PITA.
posted by porpoise at 5:56 PM on January 19, 2022 [1 favorite]
This thread is closed to new comments.
Are there any macro's / VBA code for your form? Those do not work in the web-browser version of Excel to-the-best-of my-knowledge.
Within M365, Microsoft wants you to use either "Power Apps" or the "Forms" web-based application for data collection - then you would use "Power Automate Flow" to handle logic and copy/move/merge the data (which could then end-up in an Excel spreadsheet - or another location that the spreadsheet pulls data into)
posted by rozcakj at 12:31 PM on January 19, 2022