Forum Discussion
Solved
MS Form Response Spreadsheet Formulas
I have set up a MS form and am trying to format the response spreadsheet to pull through the response data into a new sheet for editing. I have set up a formula to do this but every time a new response is collected, the formula skips that row.
Is there a way to make sure this doesn't happen?
Current formulas added.
- You should probably leave the table alone and use Data, Get Data to pull the results into a separate Excel file. Add your logic to that query.
BTW: ISBLANK(TRUE) always returns FALSE, so you might as well replace that bit with FALSE in your formula.
Perhaps you could give bit more details
- did you create the Form from Excel for web (when the file is automatically updated with responses and default sheet name is Form) or you open the file from Forms (which is generated on server)
- what is the Raw Data sheet
- if automatically updated you may add extra columns to the table, they won't affect responses
- The form was created from Excel for web so automatically updates with the responses. The raw data sheet is the Form sheet which I renamed. I essentially just want to leave the raw data as is so it doesn't get accidentally typed over or changed when people are working in the sheet.
Yes, it's better to keep raw data untouched and it's better to hide it from end users id you share with them that file. However, in general you don't need to copy raw data as it is into another sheet to make some calculations. Some could be added as additional columns with formulae to raw data, the rest, most probably aggregations, to do in separate sheet based on the table in raw data. Final sheet is reporting which you may keep opened for end users.
- What are you trying to achieve with the formula attached to data from MS Form?
Do you have the option to use SharePoint List/Survey which will help to add extra columns with calculated field?- Its mainly so that the raw data from the form remains untouched and doesn't accidentally get overwritten, and so that all data is in one place. I will look into your suggestion of a SharePoint List and see whether I can replicate my formatting.
- You should probably leave the table alone and use Data, Get Data to pull the results into a separate Excel file. Add your logic to that query.
BTW: ISBLANK(TRUE) always returns FALSE, so you might as well replace that bit with FALSE in your formula.- Thanks for your suggestion. Would this pull through any subsequent data that was collected via the form?
N_Wall2000 Yes, refreshing the query would pull in the latest responses.
