Google-sheets – Can a formula cell reference be a variable cell reference

formulasgoogle sheetsgoogle-sheets-arrayformulagoogle-sheets-datesgoogle-sheets-query

Assuming I insert every day some rows into Sheet1, this formula stops working and I have to manually edit every day — can the reference to Cell A2 and Cell E2 be a variable that remains hardcoded as for example inserting 4 rows both became A6 and E6 and the value totals are all wrong until I manually change to A2 and E2 from A6 and E6:

=ArrayFormula(QUERY(FILTER({WEEKDAY(Sheet1!A2:A),Sheet1!E2:E},ISNUMBER(Sheet1!E2:E),Sheet1!E2:E>0),"SELECT Col1, COUNT(Col1), SUM(Col2) GROUP BY Col1 ORDER BY Col1 ASC LABEL Col1 'Day', COUNT(Col1) 'Count by Day', SUM(Col2) 'Sum by Day'"))

Can A2 be some pointer to a cell that has the text "A2" and it always stays as A2 in the formula — same question for E2?

Best Answer

You mentioned:

Can A2 be some pointer to a cell that has the text "A2" and it always stays as A2 in the formula -- same question for E2 ?

You do not need to reference a pointer to an extra cell.

What you are looking for is the INDIRECT function.

You can replace your ranges
Sheet1!A2:A and Sheet1!E2:E with
INDIRECT("Sheet1!A2"&":A") and INDIRECT("Sheet1!E2"&":E")