0

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?

3
  • can you share a copy of your sheet?
    – user0
    Apr 14, 2019 at 12:03
  • Hi again! It is the very same one you helped with. Sheet1 has datetime in Col1 and a number in Col2 (or Cell A and B). The query works fine and does not break when new data inserted, it just automatically updates itself to ignore the newly inserted row.
    – rosa
    Apr 14, 2019 at 12:10
  • docs.google.com/spreadsheets/d/… ?
    – user0
    Apr 14, 2019 at 12:13

1 Answer 1

0
  • 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")}

6
  • 1
    That works. Thank you once again.
    – rosa
    Apr 14, 2019 at 12:54
  • I would add if I had the option that this is the best solution and close the question
    – rosa
    Apr 14, 2019 at 12:55
  • @rosa sorry, but you have that option! just click here: i.stack.imgur.com/prjBr.png
    – user0
    Apr 14, 2019 at 13:03
  • I see the image but not having any control over the security settings on this installed browser -- the buttons in the image are not visible on my end
    – rosa
    Apr 15, 2019 at 2:48
  • Another mystery: I added Sheet4 to the sample Google Sheets with a few test rows of data. The day 06/29/2019 is a Saturday but formula shows Monday ?
    – rosa
    Apr 15, 2019 at 2:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy