References changing in Google Sheets with new forms submissions

Question

I have a Google sheet that feeds from a Google form and then I use the sheet to make views to show data and graphics on Google sites. I noticed that suddenly the graphics said 'No Data', and when I checked, it seemed that for some reason the ranges that feed such tables had changed their referenced cells to the newest row from the submitted forms instead of retaining the range even if it was fixed.

I have no idea why or how this happened, but it happened in different places and different formulas along with the sheets, not on all but quite on many. Previously it was working fine.

This happened to formulas for 1 cell and column arrays alike. The change was merely that instead of using let's say L2:L, it changed the 2 for 54 so it ended like L54:L, even if it was fixed with $.

For example: Originally $AA$2:$AA became $AA$54:$AA

NOTE: This has affected random sections and other columns that have the same formula may not be affected, regardless as to if they refer the same columns or adjacent ones.

The following are examples of this change, where 54 should have been 2.

1: An array that works on an entire column.

    ={"Person";ArrayFormula(IF(LEN(A54:A), 'Responses'!AL54:AL&"" &                                 
    'Responses'!AK54:AK&
    " " & 'Responses'!AJ54:AJ&
    " " & 'Responses'!AI54:AI
    ,""))}

2: A mere count of responses that has a fixed range

    =COUNTIF('Responses'!$AY$54:$AY, "si")

And 3: A count for different values

    =countifs('Responses'!$AA$54:$AA,"Sí",
                'Responses'!$AD$54:$AD, "<>Rod",
                'Responses'!$AD$54:$AD, "<>Ato",
                'Responses'!$AD$54:$AD, "<>Ref",
                'Responses'!$AD$54:$AD, "<>Cua"
                )

There are no error messages.

I'm just trying to comprehend how this happened to avoid it happening again on further forms submissions (done by another person) and to correct this (hopefully not manually).

player0 player0 · Accepted Answer · 2019-07-29T20:14:57

to truly lock down the range you need to use INDIRECT:

={"Person"; 
 ARRAYFORMULA(IF(LEN(INDIRECT("A2:A")), INDIRECT("Responses!AL2:AL")&"" &
 INDIRECT("Responses!AK2:AK")&
 " " & INDIRECT("Responses!AJ2:AJ")&
 " " & INDIRECT("Responses!AI2:AI"), ))}

=COUNTIF(INDIRECT("Responses!AY2:AY"), "si")

=COUNTIFS(INDIRECT("Responses!AA2:AA"), "Sí",
          INDIRECT("Responses!AD2:AD"), "<>Rod",
          INDIRECT("Responses!AD2:AD"), "<>Ato",
          INDIRECT("Responses!AD2:AD"), "<>Ref",
          INDIRECT("Responses!AD2:AD"), "<>Cua")

References changing in Google Sheets with new forms submissions

1 Answers