How to maintain absolute cell address even when deleting ranges?
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?
you can freeze it like:
=INDIRECT("Responses!A2:R500")
instead of your:
=ARRAYFORMULA(Responses!$A$2:R500)
- Filter or map nodelists in ES6
- What is the most efficient way of getting an empty array in C# in terms of time and space?
- How do I find the closest possible sum of an Array's elements to a particular value?
- Exporting an array in bash script
- Cloning an array in Javascript/Typescript
- Remove array from JSON when it appears
- Find repeated elements occuring more than Once
- why can init array in struct with array-name in C
- How to determine if one array contains all elements of another array
- Why does my nodelist contain 0 items when console logged from my JS file?
- Reading dynamic sized byte arrays as integers
- Deep Copy with Array
- Restructure a 2d array to segment row values into associatively keyed subarrays within the row
- Insertion Sort from Right to left with Javascript
- formatting an array using php
- Getting the most frequent value of an array
- php transform array into multidim array
- Date change function in PHP
- C++ replacement for C99 VLAs (goal: preserve performance)
- Azure Logic App - For Each Key/Value Pair (Answered!)
- Difference between array_push() and $array[] =
- For loop skipping final iteration
- How to create a numpy array from a pydub AudioSegment?
- Find the 2nd highest no from array, where array contains duplicate values
- Group 2d array data by a column and apply default values where a date key is missing
- Convert a 2d array into a flat associative array using two columns
- Print 2d array data on separate lines with all row values indented except for the first value in each row
- Simplify a multidimensional array of filtering conditions using recursion
- Return result set from PDO query as an associative array from two columns
- Remove empty elements from a 2d array while inside of nested foreach loops