Google Forms changes cell reference on different sheet in Google Sheets

Question

I'm building a spreadsheet to track my macronutrients and calories. I made a google form for inputting information and it populates a "responses" sheet on google sheets. I made a different sheet within the same spreadsheet to perform all the calculation and generate graphs.

But every time I enter a new response into the form, it creates a new row and changes all the cell references in the calculations sheet. For example, I reference cell A2 from the responses

='Form Responses'!A2

and when I actually fill out the form and it populates, A2 in the responses sheet is filled in but the reference in my calculations sheet has been changed to A3.

='Form Responses'!A3

I tried using $ but it did the same thing, automatically changes the cell that I referenced.

Any way to have my calcuations sheet reference the newly created rows automatically?

Answer 1

In a new tab use for example, = {'Form Responses'! A: Z} and use this data to do your calculations and you will have no problems!

Answer 2

Solution

To avoid the row value to change reference when adding a new response, you will need to add the $ next to the number instead of next to the column value (A):

='Form Responses'!A$2

Moreover, an easy way to reference your whole column is to reference a range instead such as:

='Form Responses'!A:A

And then just drag it through your column. If you want it to be more specific (select the range you want):

='Form Responses'!A2:A999

I hope this has helped you. Let me know if you need anything else or if you did not understood something. :)