In a google sheet linked with a google form, I am putting

=ARRAYFORMULA(Responses!$A$2:R500)

in a blank sheet(namely dataList) to copy raw data from the response sheet so it is more readable and manageable.

After submitting some test data, I need to clear them and publish the form for production use. If I simply select the rows and hit "delete" on my keyboard, when new submission comes in, it will not appear on the first row(or row 2), instead it remembers how many rows there were and put the new data on the next row, thus leaving the first rows blank on both of the sheets, which is unacceptable. So I select the rows with test data in the sheet Response and delete the rows:

Now when new submission comes in, it does appear on row 2 in Sheet Response; however, when I go to my "dataList" sheet, it is like this

The A1 notation which is supposed to be absolute has been altered, hence my dataList sheet doesn't get the new submission data from sheet Response.

How to deal with this unwanted behavior?

CodePudding user response：

you can freeze it like:

=INDIRECT("Responses!A2:R500")

instead of your:

=ARRAYFORMULA(Responses!$A$2:R500)

CodePudding user response：

If you want to avoid string ranges in the previous answer, you could use INDEX:

=INDEX(Responses!A:A,2):INDEX(Responses!R:R,500)

This always takes the second row from A:A and 500th row of R:R regardless of the deleted rows.