Cross-sheet references in Google Sheets - Cheat Sheet & Quick Revision

Recall & Review

beginner

What is a cross-sheet reference in Google Sheets?

A cross-sheet reference is a formula that gets data from a different sheet within the same spreadsheet file. It helps you use information from one sheet in another.

beginner

How do you write a formula to reference cell A1 from a sheet named 'Sales'?

You write it as =Sales!A1. The sheet name, an exclamation mark, then the cell address.

beginner

What should you do if the sheet name has spaces or special characters?

Put single quotes around the sheet name, like ='Monthly Sales'!B2.

intermediate

Can you use cross-sheet references in formulas like SUM or AVERAGE?

Yes! For example, =SUM(Sheet2!A1:A10) adds values from another sheet.

intermediate

What happens if you delete or rename a sheet that a cross-sheet formula refers to?

The formula will show an error because it can’t find the sheet. You need to fix the formula or restore the sheet.

How do you reference cell B3 from a sheet named 'Expenses'?

A =Expenses!B3

B =B3!Expenses

C =Expenses:B3

D ='Expenses'B3

The correct syntax is sheet name, exclamation mark, then cell address: =Expenses!B3

If a sheet name has spaces, how should you write the reference?

A =Monthly Sales!A1

B =Monthly Sales A1

C =MonthlySales!A1

D ='Monthly Sales'!A1

Sheet names with spaces need single quotes around them: ='Monthly Sales'!A1

Which formula sums cells A1 to A5 on a sheet named 'Data'?

A =SUM(Data:A1:A5)

B =SUM(A1:A5!Data)

C =SUM(Data!A1:A5)

D =SUM('Data'!A1-A5)

The correct syntax is =SUM(Data!A1:A5) to sum cells from another sheet.

What error occurs if you reference a deleted sheet?

A #REF!

B #VALUE!

C #NAME?

D #DIV/0!

Referencing a deleted sheet causes a #REF! error because the reference is invalid.

Can you use cross-sheet references to pull data from a different spreadsheet file?

A No, cross-sheet only works within the same file

B Yes, but only with IMPORTRANGE function

C Yes, with simple syntax

D No, you must copy data manually

To get data from another spreadsheet file, use IMPORTRANGE, not simple cross-sheet references.

Explain how to write a formula that references a cell from another sheet, including if the sheet name has spaces.

Think about the syntax with and without spaces in sheet names.

Describe what happens when you delete or rename a sheet that a cross-sheet formula refers to and how to fix it.

Consider what the formula is trying to find and what happens if it can't.

Practice

1. What is the correct way to reference cell B2 from a sheet named Sales in another sheet?

easy

A. =Sales!B2

B. =B2!Sales

C. =Sales:B2

D. =Sheet2.B2

Solution

1. Step 1: Understand cross-sheet reference format

   In Google Sheets, to reference another sheet's cell, use SheetName!Cell.

2. Step 2: Apply the format to the example

   For sheet named Sales and cell B2, the reference is =Sales!B2.

3. Final Answer:

   =Sales!B2 -> Option A

4. Quick Check:

   SheetName!Cell = =Sales!B2 [OK]

Hint: Use SheetName!Cell to link sheets quickly [OK]

Common Mistakes:

• Swapping sheet name and cell reference order
• Using colon instead of exclamation mark
• Adding extra dots or symbols

2. Which of these formulas correctly references cell A1 from a sheet named 2023 Data?

easy

A. ='2023 Data'!A1

B. =2023 Data!A1

C. =2023_Data!A1

D. =2023Data!A1

Solution

1. Step 1: Recognize sheet names with spaces need quotes

   Sheet names with spaces must be enclosed in single quotes when referenced.

2. Step 2: Apply quotes to the sheet name

   The correct reference is ='2023 Data'!A1.

3. Final Answer:

   ='2023 Data'!A1 -> Option A

4. Quick Check:

   Sheet names with spaces need quotes [OK]

Hint: Put single quotes around sheet names with spaces [OK]

Common Mistakes:

• Omitting quotes for sheet names with spaces
• Using underscores instead of spaces without quotes
• Using invalid characters in sheet names

3. Given the formula =SUM('Expenses 2024'!C2:C5) in Sheet1, what does it calculate?

medium

A. Sum of cells C2 to C5 in the current sheet

B. Sum of cells C2 to C5 in the sheet named Expenses2024 (no space)

C. Sum of cells C2 to C5 in all sheets

D. Sum of cells C2 to C5 in the sheet named 'Expenses 2024'

Solution

1. Step 1: Identify the sheet name in the formula

   The formula references the sheet named Expenses 2024 with quotes because of the space.

2. Step 2: Understand the range and function

   It sums cells from C2 to C5 in that specific sheet.

3. Final Answer:

   Sum of cells C2 to C5 in the sheet named 'Expenses 2024' -> Option D

4. Quick Check:

   SUM('Sheet Name'!Range) sums that sheet's range [OK]

Hint: Quotes mean exact sheet name with spaces [OK]

Common Mistakes:

• Assuming sum is from current sheet
• Ignoring quotes and misreading sheet name
• Thinking it sums multiple sheets

4. You wrote the formula =Sales!B2 in Sheet2, but it shows #REF!. What is the likely cause?

medium

A. You used a wrong cell reference format

B. Cell B2 is empty in the Sales sheet

C. The sheet named 'Sales' does not exist

D. You forgot to add quotes around Sales

Solution

1. Step 1: Understand what #REF! means

   #REF! error usually means the reference is invalid or missing.

2. Step 2: Check if the referenced sheet exists

   If the sheet named 'Sales' does not exist, the formula cannot find it, causing #REF!.

3. Final Answer:

   The sheet named 'Sales' does not exist -> Option C

4. Quick Check:

   #REF! = Missing or invalid reference [OK]

Hint: Check if referenced sheet exists to fix #REF! [OK]

Common Mistakes:

• Assuming empty cell causes #REF!
• Adding quotes unnecessarily when no spaces
• Mistaking #REF! for syntax error

5. You want to create a summary sheet that shows the total sales from multiple sheets named Jan, Feb, and Mar. Which formula correctly sums cell D10 from all three sheets?

hard

A. =SUM(Jan:Mar!D10)

B. =Jan!D10 + Feb!D10 + Mar!D10

C. =SUM('Jan','Feb','Mar'!D10)

D. =SUM(Jan!D10:Mar!D10)

Solution

1. Step 1: Understand cross-sheet range syntax

   Google Sheets supports 3D ranges like Jan:Mar!D10 only if the sheets are consecutive tabs named Jan, Feb, Mar in order.

2. Step 2: Sum individual references explicitly

   Adding each sheet's cell individually like =Jan!D10 + Feb!D10 + Mar!D10 works reliably regardless of tab order.

3. Final Answer:

   =Jan!D10 + Feb!D10 + Mar!D10 -> Option B

4. Quick Check:

   Sum separate sheet cells by adding each reference [OK]

Hint: Add each sheet's cell separately to sum across sheets [OK]

Common Mistakes:

• Using invalid 3D range syntax
• Trying to sum sheets with commas inside SUM
• Assuming colon works for non-adjacent sheets