References to another sheet or workbook
A formula can also take a cell from another sheet: first the sheet name, then an exclamation mark, then the address, for example =Sheet2!B3. If the sheet name contains a space, the name goes in apostrophes: ='Q 1'!B3. A cell in another workbook is written with the file name in square brackets: =[Sales.xlsx]Sheet1!$B$3; if the file is closed, Excel also shows the full path. You do not have to type the reference: press “=”, go to the sheet you need and click the cell. To add up cells in the same place on several sheets there is also an entry such as =SUM(Sheet1:Sheet3!B3). If you paste copied cells with “Paste Special – Paste Link”, the result changes when the source changes; links are managed in the “Edit Links” window on the Data tab. If Excel warns about updating links when a file opens, allow it only for files you trust, because a foreign file can pull data from unexpected sources through its links.
| Form | Refers to |
|---|---|
| =Sheet2!B3 | another sheet |
| ='Q 1'!B3 | a sheet name with a space |
| =[Sales.xlsx]Sheet1!$B$3 | another workbook |
| =SUM(Sheet1:Sheet3!B3) | the same cell on several sheets |
For a school sports day each class teacher keeps a list on a separate sheet. The organiser’s summary sheet takes each class total from those sheets. When one class list changes, the summary number updates by itself.
Accountants combine branch reports, teachers gather class lists and managers see the results of departments in one place.
Write one number on each of three sticky notes, write “1st + 2nd + 3rd” on a fourth, and explain why the total must change if you change the first number.
| Sheet | C5 |
|---|---|
| Yanvar | 150 |
| Fevral | 95 |
| Mart | 60 |
| Chorak | =Yanvar!C5+Fevral!C5+Mart!C5 = 305 |
| File | Baho!D4 |
|---|---|
| Kimyo.xlsx | 80 |
| Biologiya.xlsx | 70 |
| Tarix.xlsx | 90 |
| mean | 80 |
“The collectors”: each pair writes marks for 3 subjects in their own workbook, and then one pair collects the marks from all the files into a common workbook with Paste Link. We change a mark in a source and watch the common workbook update.