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.