The price changed in one place
A shop keeps prices on one page and orders on another. The price of a lock goes up.
Should somebody retype every order? No. The orders page can point at the price page, so it updates by itself.
Key words
| Word | What it means | Example |
|---|---|---|
| Worksheet | one page of cells in a spreadsheet file | |
| Linking | a cell that reads its value from another worksheet | |
| Lookup | finding a value in a list by matching a key | XLOOKUP |
| CSV file | a plain text file of values split by commas | |
| Export | saving data out of a program as a file | |
| Import | reading a file into a program | |
| Pivot table | a summary that regroups rows and adds them up |
The cells students fill in are left blank in this preview.
By the end of this lesson
Note
I can describe how worksheets link, how files move in and out, and what a pivot table does.
Starter
Have a go
Starter
3 questionsYour answers are marked here and are not kept. A college account keeps every student’s marks.
Linking reads one sheet from another
A cell can read from another worksheet. In many programs you write the sheet name, then the cell, such as Prices!B2.
A lookup finds a value by matching a key. For example, find "lock" in the Prices sheet and bring back its price.
Two worksheets and the formula that links them
| Orders sheet | A: product | B: units | C: line total (formula) |
|---|---|---|---|
| Row 2 | helmet | 4 | B2 times the lookup of A2 in Prices |
| Row 3 | lock | 10 | B3 times the lookup of A3 in Prices |
| Row 4 | bell | 6 | B4 times the lookup of A4 in Prices |
The cells students fill in are left blank in this preview.
Spreadsheets are used on site
Important
This platform has no spreadsheet program. The grid above shows the idea, and the next block does the same job with code. In work you use a real spreadsheet program.
Try it: the lookup as a join
-- Match each order to its price, then multiply.
select o.row_no, o.product, o.units * p.price_pence as line_pence
from orders_sheet o
join prices_sheet p on p.product = o.product
order by o.row_no;
-- Try it: sum all the line totals instead.Check
Have a go
Check
3 questionsYour answers are marked here and are not kept. A college account keeps every student’s marks.