Lessons 5–6 · 2 lessons · N. I. Taylaqov (general editor), A. B. Akhmedov, M. D. Pardayeva, A. A. Abdug‘aniyev, U. M. Mirsanov. Informatics and Information Technologies, Grade 10, 1st edition. “Extremum-Press”, Tashkent, 2017
5
References to another sheet or workbook
Textbook: pp. 14–18
GoalWrites a reference to a cell on another sheet or in another workbook and works safely with linked files.
New words
sheet reference: the sheet name, an exclamation mark and the cell, for example Sheet2!B3 · varaqqa murojaatlink between workbooks: a formula that takes a value from another file · kitoblararo bog‘lanishPaste Special: a paste with options, such as Paste Link · maxsus qo‘yishEdit Links: the dialog that updates, changes or breaks links to other files · bog‘lanishlarni tahrirlash
Explanation
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.
Worked examples
A quarterly report: Yanvar!C5 = 150, Fevral!C5 = 95, Mart!C5 = 60 (notebooks sold). On the “Chorak” sheet C5 gets =Yanvar!C5+Fevral!C5+Mart!C5 and shows 305. The same result is given by =SUM(Yanvar:Mart!C5). Copied to neighbouring cells, each cell adds the matching cells of the same three sheets.
Marks in three files: [Kimyo.xlsx]Baho!D4 = 80, [Biologiya.xlsx]Baho!D4 = 70, [Tarix.xlsx]Baho!D4 = 90. The formula =([Kimyo.xlsx]Baho!D4+[Biologiya.xlsx]Baho!D4+[Tarix.xlsx]Baho!D4)/3 in the summary file gives the mean 80. If a mark changes in a source file, the summary file shows the new mean at the next update.
Class activity
“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.
Practice
1
How is a reference to cell C3 on the sheet “Mart” written?
=Mart!C3
2
If the sheet is named “Q 1” (with a space), write a reference to its cell C3.
='Q 1'!C3
3
150, 95 and 60 notebooks were sold in three months. How many in the quarter?
305
4
Why should you not rush to update links when a foreign file is opened?
The file may pull data from unexpected sources through its links; permission is given only to trusted files.