Method to preserve cell reference in a formula upon adding rows in reference sheet?

Question

I have a Google Sheets with two(2) tabs. The first sheet is just a running tally of raw data and is updated with new rows at the top weekly.

In tab 2 there is just a single query(arrayformula)... that references Sheet1!A2 cell dozen times. The entire formula is 816 characters in length.

Each time I add a single row to tab 1, the formula still works in tab 2 but it now automatically changes all references to Sheet1!A2 to Sheet1!A3 and ignores the inserted row data until manually edited.

Is there a method to avoid manual edit when inserting rows?

Answer 1

• lock it with INDIRECT and try like this:

={QUERY(ARRAYFORMULA(TEXT(SUBSTITUTE(TEXT(UNIQUE(IF(LEN(INDIRECT("Sheet2!A2:A")),
 DATE(20&MID(INDIRECT("Sheet2!A2:A"), 7, 2), LEFT(INDIRECT("Sheet2!A2:A"), 2), 
 MID(INDIRECT("Sheet2!A2:A"), 4, 2)), )),
 "dd/mm/yyyy"), "30/12/1899",""), "dddd")),
 "select Col1,count(Col1) where Col1<>'' group by Col1 label count(Col1)''"),
 QUERY(QUERY({ARRAYFORMULA(IF(LEN(INDIRECT("Sheet2!A2:A")),
 TEXT(DATE(IF(LEN(INDIRECT("Sheet2!A2:A")),20,)&MID(INDIRECT("Sheet2!A2:A"),7,2),
 LEFT(INDIRECT("Sheet2!A2:A"),2),MID(INDIRECT("Sheet2!A2:A"),4,2)),"dddd"),)),
 INDIRECT("Sheet2!B2:B")},
 "select Col1,sum(Col2) group by Col1 label sum(Col2) ''"),
 "select Col2 where Col1 is not null")}