Three months, one total
The shop keeps one sheet for each month. The owner wants the North branch total for the quarter. You could add three cells by hand.
But next month there will be a fourth sheet. A better way reaches across all of them with one reference.
Key words
| Word | What it means |
|---|---|
| Cell reference | the name of a cell, such as B2: column B, row 2 |
| 3D reference | a reference that picks the same cell on a run of sheets |
| Absolute reference | a reference that stays fixed when a formula is copied, written with $ |
| Worksheet | one page of cells in a spreadsheet file |
| Locked cell | a cell that can not be changed while its sheet is protected |
| Protection | a setting that stops people changing locked cells |
The cells students fill in are left blank in this preview.
By the end of this lesson
Note
I can read cell and 3D references, choose which cells to lock, and do the same jobs in code.
Starter
Have a go
Starter
3 questionsYour answers are marked here and are not kept. A college account keeps every student’s marks.
Real spreadsheets are used on site
Important
This platform has no spreadsheet. The sheets below are tables of cells, with each formula written as text. Practise the real thing, such as 3D references and sheet protection, in a spreadsheet program on site.
This is practice
Important
The real task comes from the exam board. This lesson uses an invented organisation and its own made-up data. Your teacher tells you how the real tasks are set and what you may bring.
Worked example: three sheets of cells
| Sheet | A1 holds | B1 holds | A2 holds | B2 holds |
|---|---|---|---|---|
| Jan | Branch | Total in pence | North | 12000 |
| Feb | Branch | Total in pence | North | 13500 |
| Mar | Branch | Total in pence | North | 11000 |
| Quarter | Branch | Total in pence | North | `=SUM(Jan:Mar!B2)` |
The cells students fill in are left blank in this preview.
Run it: the same job in SQL
SELECT branch, SUM(total_pence) AS quarter_pence
FROM (
SELECT * FROM jan
UNION ALL SELECT * FROM feb
UNION ALL SELECT * FROM mar
)
GROUP BY branch
ORDER BY branch;How a 3D reference reads
The formula reads: add cell B2 on every sheet from Jan to Mar. The sheets must sit side by side in the file.
Add an Apr sheet between Jan and Mar, and it is counted too. Add it after Mar, and you must change the formula. The answer for North is 36500 pence.