Solved: Google Sheets Range Reference via Cell Reference

📌 The Problem Explained

Imagine you are building a dynamic Monthly Sales Dashboard. You have multiple sheets named “January”, “February”, and “March”. Instead of writing a new formula for every month, you want to simply type the sheet name into cell A1 and have your formulas automatically pull data from that specific sheet.

Standard cell references are “static”—if you write =Sum(January!B:B), it will always point to January. To make a formula point to a range that is defined by the text inside another cell, you need a way to turn a text string into a functional reference.

💡 The Quick Solution

The solution is the INDIRECT function. This function evaluates a string of text as a cell or range address.

=SUM(INDIRECT(A1 & "!B:B"))

(In this example, if cell A1 contains the word “January”, the formula calculates the sum of column B on the “January” sheet.)

🛠️ Step-by-Step Breakdown

To implement a range reference via a cell reference, follow these steps:

Step	Action	Description
1	Prepare the Text	Type the name of the sheet or the range (e.g., A1:A10) into a control cell (e.g., C1).
2	Start the Function	Begin your formula (e.g., =SUM(, =VLOOKUP(, or =AVERAGE().
3	Insert INDIRECT	Type INDIRECT( where you would normally put the range.
4	Define the String	Reference your control cell. If referencing a different sheet, use the ampersand & to join the sheet name with the range: INDIRECT(C1 & "!A:A").
5	Close & Enter	Close the parentheses for both the INDIRECT function and your primary function.

⚠️ Common Mistakes to Avoid

• Missing Quotation Marks: When combining a cell reference with a static range (like a column letter), the static part must be in quotes.
  • Wrong: INDIRECT(A1 & B1:B10)
  • Right: INDIRECT(A1 & "!B1:B10")
• Spaces in Sheet Names: If your sheet names contain spaces (e.g., “Sales Data”), the formula will fail unless you wrap the sheet name in single quotes.
  • Pro Tip: Use INDIRECT("'" & A1 & "'!B:B") to safely handle sheet names with or without spaces.
• Case Sensitivity: While sheet names aren’t case-sensitive in Google Sheets, the text in your reference cell must match the sheet name exactly regarding spelling.