AdvertisementYour brand could be hereYour brand hereOn every Darslab page · students, parents and teachersinfo@darslab.comAsk for pricesPricesinfo@darslab.com
☰ Contents · Computing

Cross-sheet references and functions

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.

FormRefers to
=Sheet2!B3another sheet
='Q 1'!B3a sheet name with a space
=[Sales.xlsx]Sheet1!$B$3another workbook
=SUM(Sheet1:Sheet3!B3)the same cell on several sheets
💡 Where will I use this?
In real life

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.

Who uses it

Accountants combine branch reports, teachers gather class lists and managers see the results of departments in one place.

Try it today

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.

Worked examples
SheetC5
Yanvar150
Fevral95
Mart60
Chorak=Yanvar!C5+Fevral!C5+Mart!C5 = 305
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.
FileBaho!D4
Kimyo.xlsx80
Biologiya.xlsx70
Tarix.xlsx90
mean80
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?
2
If the sheet is named “Q 1” (with a space), write a reference to its cell C3.
3
150, 95 and 60 notebooks were sold in three months. How many in the quarter?
4
Why should you not rush to update links when a foreign file is opened?
Story of the Week · The Khwarizmi Way“Sugar-free”: carbohydrate-free?Close the week: all of this week’s subjects in one storyRead →