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 '19 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 '19 at 12:10
  • docs.google.com/spreadsheets/d/… ?
    – user0
    Apr 14 '19 at 12:13
  • lock it with INDIRECT and try like this:

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