How to reference a fixed sheet name in Google Spreadsheet

Question

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.

Answer 1

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.

Answer 2

I would use something like the =importrange() function in Google Sheets because the referenced sheet in that formula doesn't change.

The formula needs two things - a Spreadsheet Key, which is specific to the entire workbook, and a range to import.

The Spreadsheet Key is in the URL:

https://docs.google.com/spreadsheets/d/SPREADSHEET KEY HERE/edit#gid=1042908258

Then, for the range, pick whatever range of cells you want to pull in from the raw sheet in the book.

A sample could be: =importrange("SPREADSHEET KEY", "Raw!A1:A")

That formula will pull data from Column A as long as the sheet is called "Raw". This formula will not change when you rename a sheet in the book because it doesn't matter which sheet is named "Raw", it just looks for one.