Referencing a cell on another worksheet
Name:
Date:
Class:
A
Quick Questions
Shade in the A or B circle for each question.
1. Which formula pulls the value from cell B2 on a sheet named Data?
2. Which symbol separates the sheet name from the cell address in a reference?
3. A sheet is named Sales Data. Which formula correctly refers to its cell B2?
4. Your Dashboard sheet needs to show the total of Data!B2:B9. Which formula should go on the Dashboard?
5. You type =B2 on the Dashboard sheet. Where does it take its value from?
B
Match the Pairs
Draw a line connecting each item on the left to its match on the right.
| Left column | Right column | |
|---|---|---|
| 1=Data!B2 | A=Data!C5 | |
| 2=SUM(Data!B2:B9) | B='Sales Data'!B2 | |
| 3A sheet name that contains a space must be wrapped in single quotes before the exclamation mark | CA full stop is used instead of an exclamation mark between the sheet name and the cell. | |
| 4A Dashboard sheet cell shows the value of cell C5 on the Data sheet | DAdds up the range B2:B9 on the sheet named Data | |
| 5=Sales Data!B2 | EPulls the value from cell B2 on the sheet named Data | |
| 6=Data.B2 | FThe sheet name has a space but no single quotes around it, so the formula gives an error. |
C
Definitions
=Data!B2
D
Fill in the Blanks
Complete each line of code by filling in the missing parts.
1.
=Data_B2
2.
=____!B2+Data!B3
3.
=_______('Sales Data'!B2:B9)
4.
=___(Data!B2:B9)
5.
=SUM(Data_B2:B9)
E
Spot the Error
Shade in the letter of the line that contains an error.
Question 1
A
A1: =Data!B2
B
A2: =Data.B3
C
A3: =Data!B4
Question 2
A
Dashboard sheet, column B
B
B1: =Data!B2
C
B2: =Data!B3
D
B3: =SUM(B2:B9!Data)
Question 3
A
A1: ='Sales Data'!B2
B
A2: ='Sales Data'!B3
C
A3: =Sales Data!B4
Question 4
A
Dashboard sheet
B
B1: =SUM(Data!B2:B9)
C
B2: =AVERAGE(Data,B2:B9)
D
B3: =MAX(Data!B2:B9)
Question 5
A
A1: ='Term 1'!C2
B
A2: ='Term 1'!C3
C
A3: ="Term 1"!C4
Answer Key
Referencing a cell on another worksheet
Section A: Quick Questions
Q1
B — =Data!B2
Q2
B — ! (exclamation mark)
Q3
A — ='Sales Data'!B2
Q4
A — =SUM(Data!B2:B9)
Q5
A — Cell B2 on the Dashboard sheet
Section B: Match the Pairs
Q1
=Data!B2 → Pulls the value from cell B2 on the sheet named Data
Q2
=SUM(Data!B2:B9) → Adds up the range B2:B9 on the sheet named Data
Q3
A sheet name that contains a space must be wrapped in single quotes before the exclamation mark → ='Sales Data'!B2
Q4
A Dashboard sheet cell shows the value of cell C5 on the Data sheet → =Data!C5
Q5
=Sales Data!B2 → The sheet name has a space but no single quotes around it, so the formula gives an error.
Q6
=Data.B2 → A full stop is used instead of an exclamation mark between the sheet name and the cell.
Section C: Definitions
Q1
=Data!B2 — Pulls the value from cell B2 on the sheet named Data. Write the sheet name, then !, then the cell. Names with spaces need quotes: ='Sales Data'!B2.Section D: Fill in the Blanks
Q1
=Data!B2Q2
=Data!B2+Data!B3Q3
=AVERAGE('Sales Data'!B2:B9)Q4
=SUM(Data!B2:B9)Q5
=SUM(Data!B2:B9)Section E: Spot the Error
Q1
B — A full stop is used instead of an exclamation mark. The sheet name and the cell must be separated by !, so it should be =Data!B3.
Q2
D — The sheet name must come first, followed by !, then the range. It should be =SUM(Data!B2:B9).
Q3
C — A sheet name containing a space needs single quotes around it. It should be ='Sales Data'!B4.
Q4
C — A comma is used where the exclamation mark should be. Without !, the formula does not point to the Data sheet. It should be =AVERAGE(Data!B2:B9).
Q5
C — Sheet names are wrapped in single quotes, not double quotes. It should be ='Term 1'!C4.