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

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.

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.

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: