Categories: Share an idea : Sheets :

Can you stop spreadsheet survey forms messing up formulas by inserting new responses?

Showing 1-6 of 6 messages
Can you stop spreadsheet survey forms messing up formulas by inserting new responses? j.boults 12/8/10 4:27 PM
Here's the issue: I want a completely web-based spreadsheet to collect student data through surveys, and then a teacher can produce reports for specific students using a drop down menu at any time.

It works perfectly as long as no additional surveys are collected (small issue there...) You see, when a new survey is submitted, all the rows shift down on the survey response page, and mess up all the existing formulas in the file. 

Is there a way around this?


Using:
Mac OSX snow leopard 
Free version of Google Docs:




Re: Can you stop spreadsheet survey forms messing up formulas by inserting new responses? HarbyNotts 12/9/10 1:44 AM
You can prevent the form from accepting new responses by unticking the menu item Form>Accepting responses

On the other hand, it is expected that the forms response would generate new rows, and therefore the reporting formulae could/should be constructed so as to allow for this.

For example, if you were to on a spare sheet, and assuming that your form responses were arriving on Sheet1) put in a formula in cell A2 which went something like this: =SORT(Sheet1!A2:Z,1,0) then the latest reponse would always be at the top of this sheet, no matter how many form returns were added. 

But there is quite a bit more that should be done, typically using ARRAYFORMULA which will allow there to be as many forms as you want without needing to readjust any formulas as new rows come in.

If you could share a spreadsheet which represents the situation you are talking about, for example a copy of the actual spreadsheet, or one with dummy data, but structured as you would wish, then people on the forum will be more likely to quickly give you help.
Re: Can you stop spreadsheet survey forms messing up formulas by inserting new responses? Gill 12/9/10 5:53 AM
@HarbyNotts - I am intrigued by your nick - would I be right in thinking it is a village name?
Re: Can you stop spreadsheet survey forms messing up formulas by inserting new responses? HarbyNotts 12/9/10 6:54 AM
Gill, you are very right!
Re: Can you stop spreadsheet survey forms messing up formulas by inserting new responses? Gill 12/9/10 4:00 PM
Aha :-) Thanks for confirming..
Re: Can you stop spreadsheet survey forms messing up formulas by inserting new responses? KirkinK 12/11/10 5:42 AM
I read about using ARRAYFORMULA elsewhere but, at first, it seemed very difficult.  I later figured out a very simple way of using ARRAYFORMULA to solve the problem.  You can see how I did it on my spreadsheet, here:
You can see the form, which also has a link to this public spreadsheet on it, here:
Notice that the chart on the blog continues to update as new data comes in.