Google Sheets – Referencing a Fixed Sheet Name

google sheetsworksheet-function

I am doing reports from a set of data in Google Spreadsheet. As of now, I have several sheets, among them Raw Data and Processed Data.

In my Processed Data sheet, I use formulas such as: SUM('Raw Data'!A:A). However, if I rename the Raw Data sheet to Raw Data (old), all my formulas are changed to SUM('Raw Data (old)'!A:A).

While I find this a really nice feature, I was wondering if there was a way to force spreadsheet to reference the sheet named Raw Data, in a similar fashion $ works for cells, so that I can import my new data weekly and simply rename the newly imported sheet.

Best Answer

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.

Related Topic