Separating responses to different Sheets in Google Sheets

Question

https://docs.google.com/spreadsheets/d/1oWXZYCoKK_dqu8ONcTGqOU4pNBlypi3X7ES7uH7vcNQ/edit?usp=sharing (Disclaimer: First time ever using Sheets/Forms and I am not great with Excel Either)

I have a Google Sheets file that takes all the responses from a google form and sorts them by foreman name. I am running into a problem however. The formula I have used seems to work and then when I close the sheet and open it the next day it keeps moving the cell range down to the line right below the data. This is the formula I am using

=FILTER('Form Responses 1'!$A$2:BE,'Form Responses 1'!$BE$2:BE="William Kirby")

I tried to use $ like in excel to lock that range but I don't think its working. I just went back through and changed them all to what they should be again, but I need that formula to stay what it is at all times. I left the last foreman on the list wrong so you could see what it changes to. His name is Zach Habron.

Any advice would be much appreciated

-Parker

Answer 1

Your range references get adjusted because form responses are always inserted in newly created rows that cannot be referenced directly in advance.

One easy way to avoid the issue is to make the range reference start at row 1 instead of row 2, like this:

=query('Form Responses 1'!A1:BE, "where BE = 'Andrew Hite' ", 1)

More generally, the usual recipe to work around this issue is to Insert > Sheet and use a formula in cell A1 to copy all responses, like this:

={ 'Form Responses 1'!A1:BE }

Then point your formulas in other sheets to the new sheet instead of the form responses sheet. The references will no longer get adjusted as new responses come in.

You could also consider an alternative way of viewing data by different types: instead of using formulas to scatter the data into many separate viewing sheets, like you do now, use filter views directly on the form responses sheet to choose which rows to show.

This approach would let you, and other users, view and edit one particular kind of data, without disturbing others, and without leaving the data sheet. Importantly, filter views let you edit data right there while you are viewing the filtered results.

For additional ease of use, you can insert links in the frozen section of the sheet to easily switch between filter views, instead of having to go to Data > Filter views to switch. See the Filter views example spreadsheet for an illustration.