While the formula added by Brian Bennet works, it takes a significant amount of time to update and feels somewhat clunky in an arrayformula.
Instead, I used INDIRECT
to solve my problem :
A | B | C
------------------------------------------------------------
1| =SUM(INDIRECT("'Raw Data'!A:A")) | |
2| =SUM(INDIRECT(B2&"A:A")) | ="'Raw Data'!" |
3| | |
On the column A
of this example, A1 is what I used for quick formulas, and A2 allowed for more modularity
However, as I used this formula inside ARRAYFORMULA
, the refresh time of the array became erratic, especially when the sheet doesn't exist at first.
When the sheet doesn't exist, all cells in the arrayformula are marked #N/A
. When I add the sheet Raw Data
, even if I refresh my page with F5 several times, I still got the #N/A
. I am unsure how to force the refresh. A few minutes afterwards however, the data is updated, and I can edit my sheet & the arrayformula is calculated instantly (calculation time is only very slightly impacted).
This might be another issue not necessarily related to my initial question, but I thought I should mention this behavior anyway.
As a side note : this fix doesn't work for pivot tables. I am still looking for a solution for that case.