1

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

0

1 Answer 1

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.

2
  • So if I were to add a new sheet and point all the formulas in those sheets to the new sheet wouldnt it cause me the same issue? As this new sheet, (lets call it 'All Inspections') pulls data from the 'Form Responses 1' sheet it would be adding rows as well. Or since its not directly linked to the google form does it behave differently? Thank you for you help and I will be implementing this trick and I will let you know how it works out
    – Parker
    Feb 13, 2023 at 16:42
  • That is correct. Nevertheless, I would recommend filter views instead of scattering data across many tabs. Feb 13, 2023 at 17:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.