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?

  • can you share a copy of your sheet? – user0 Apr 14 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 at 12:10
  • docs.google.com/spreadsheets/d/… ? – user0 Apr 14 at 12:13
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")}

  • 1
    That works. Thank you once again. – rosa Apr 14 at 12:54
  • I would add if I had the option that this is the best solution and close the question – rosa Apr 14 at 12:55
  • @rosa sorry, but you have that option! just click here: i.stack.imgur.com/prjBr.png – user0 Apr 14 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 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 at 2:53

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.