One of our teams has been using a form to capture quality coaching data. The form data has been saving to an Excel workbook stored in the SharePoint site/group where the form currently resides. The team has added columns to the form data to do some simple scoring calculations and that's been working well for months, with those added formulas auto-populating to new rows as new responses are captured.
The team has also been moving data out of that Forms connected workbook into its own workbook monthly so that they have a snapshot of that month's data in its own file, and also to keep the main file from getting overly large. Basically, they just copy an entire months' worth of submissions into a new workbook, then delete those copied rows from the original workbook being used by the form. That's worked without issue for several months.
At the beginning of this month, the team did their monthly copy and delete activity without issue. However, when they went in a few days later, they found that the form had saved a few more responses but no longer seemed to be capturing new response data. the found that there was now a second Excel workbook in the same SharePoint space using the same name but with a "1" added to the end, and that was where all the most recent responses were being stored. We confirmed that the "Open in Excel" link in the form's Responses tab was now opening this new workbook, whereas before it had always opened the original one.
I checked the version history of both files and confirmed that the only updates that occurred to the old file right before the new file was created were all just new responses coming from the form itself. There were also edits after the new file created, but those were from the team going into the original file and trying to figure out why there was no new response data showing up, before realizing there was a second file in play.
I've searched the internet for information on what could cause Forms to choose to create a new Excel workbook for storing an existing form's data and abandoning the old file entirely, but I have found nothing on that topic. During that search, I did find discussions suggesting there is no way to manually modify the Excel file a form is using to store its response data. So it seems like we're stuck with the data being split across two files now.
I'm hoping someone here has more information on what kinds of activities might have caused this form to start a new workbook for its data storage, mostly so we can avoid this happening again.
