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!