1

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: enter image description here

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 enter image description here

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?

player0
  • 124,011
  • 12
  • 67
  • 124
shenkwen
  • 3,536
  • 5
  • 45
  • 85

2 Answers2

3

you can freeze it like:

=INDIRECT("Responses!A2:R500")

instead of your:

=ARRAYFORMULA(Responses!$A$2:R500)
player0
  • 124,011
  • 12
  • 67
  • 124
2

If you want to avoid string ranges or INDIRECT, 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.

Advantage:

This can be drag filled. It can change based only on certain conditions.

TheMaster
  • 45,448
  • 6
  • 62
  • 85
  • Thanks. What does "string ranges" refer to? – shenkwen Oct 04 '22 at 18:36
  • @shenkwen "A1:A5" is a string. Anything within double quotes is a string. They're never treated as ranges except when evaluated by `INDIRECT` – TheMaster Oct 04 '22 at 18:37
  • Thanks. In the scope of my case, in what condition would I want to avoid using them? – shenkwen Oct 04 '22 at 18:44
  • 1
    @shenkwen In the scope of your case, you really don't have to avoid it. In [tag:excel], `INDIRECT` is a volatile function(it makes the sheet do unnecessary overwork). In [tag:google-sheets], no specific information is given about `INDIRECT`(whether it causes overload or reduces optimization is unclear). But I'll avoid it except when there's no other choice. – TheMaster Oct 04 '22 at 18:47
  • Thanks. I accepted the other answer because his is the first, but your answer and comments are also very helpful! – shenkwen Oct 04 '22 at 18:49
  • 1
    @shenkwen Related: https://stackoverflow.com/questions/59400613/does-google-sheets-have-volatile-functions-like-excel – TheMaster Oct 04 '22 at 18:50
  • Thanks. Good read. I do have a sheet to which I probably put too many formulas to a point every time when I try to load version history it returns an error and it could take as long as 1 minute to recalculate when I update a cell. I don't know which formulas are causing the issue though. – shenkwen Oct 04 '22 at 19:02