Complex Formulas and Functions

Lessons

Senior 2 · ICT

Complex Formulas and Functions

5 lessons

Lesson 2 of 5

Referencing Other Worksheets

15 min

Let's begin

A class teacher keeps a separate sheet for each term: Term1, Term2 and Term3. At the end of the year she needs a Summary sheet with each student's total for the whole year. Copying numbers by hand would be slow, and the copies would go out of date whenever a mark is corrected. Instead, a formula on the Summary sheet can read the cells on the other sheets directly.

Writing a reference to another sheet

A reference to a cell on another sheet names the sheet first, then the cell. The separator is different in the two programs:

Program	Separator	Cell E2 on sheet Term1	Sheet name with a space
LibreOffice Calc	full stop (.)	=Term1.E2	='Term 1'.E2
Microsoft Excel	exclamation mark (!)	=Term1!E2	='Term 1'!E2

A sheet name that contains a space must be put in single quotation marks. Calc can also be set to use Excel's style, in Tools > Options > LibreOffice Calc > Formula.

The easy way: point and click

On the Summary sheet, click the cell for the answer and type =.
Click the Term1 sheet tab, click cell E2, and type +.
Click the Term2 tab, click E2, type +; click the Term3 tab, click E2.
Press Enter. You are returned to the Summary sheet with the result.

The program writes the references for you. Calc may show them as $Term1.E2: the $ in front of the sheet name only locks the sheet, so it will not change if you copy the formula.

Worked example: Uwase's year total

Uwase's term totals are in cell E2 of each term sheet: 232 on Term1, 240 on Term2 and 251 on Term3.

plaintext

1

2

3

Year total (Calc):   =Term1.E2+Term2.E2+Term3.E2            -> 723
Year total (Excel):  =Term1!E2+Term2!E2+Term3!E2            -> 723
Term average (Calc): =AVERAGE(Term1.E2,Term2.E2,Term3.E2)   -> 241

Code Explanation

232 + 240 + 251 = 723, and 723 ÷ 3 = 241. AVERAGE is a function that finds the mean; you will meet it properly in the next lesson. If a mark is corrected on the Term2 sheet, the Summary sheet updates at once.

What happens when sheets change

If you rename a sheet, formulas that refer to it are updated automatically to the new name.
If you delete a sheet, formulas that referred to it show the error #REF!, meaning the reference no longer exists.

Tip

Sheet, separator, cell. Calc uses a full stop (Term1.E2), Excel an exclamation mark (Term1!E2). If you are unsure, type = and click: the program writes it correctly for you.

✦

Practice

1

In Calc, write a reference to cell B7 on a sheet called Fees.

Show solutionHide solution

Solution: =Fees.B7

2

Write the same reference for Excel.

Show solutionHide solution

Solution: =Fees!B7

3

The sheet is called "Term 2". Why does =Term 2.E2 fail in Calc, and what is correct?

Show solutionHide solution

Solution: The space breaks the name. Put it in single quotes: ='Term 2'.E2

4

The Summary sheet shows #REF! after a clean-up. What probably happened?

Show solutionHide solution

Solution: A sheet that the formulas referred to was deleted, so those references no longer point anywhere.

Remember

Calc: =Sheet.Cell (Term1.E2). Excel: =Sheet!Cell (Term1!E2). Put names with spaces in single quotes.
Typing = and clicking on the other sheet builds the reference for you.
Renaming a sheet updates the formulas; deleting it gives #REF!.

Sources and credits

Getting Started Guide 26.2, Chapter 3: Getting Started with Calc, LibreOffice Documentation Team, The Document Foundation, licensed under CC BY 4.0. Adapted by Ganzaa.
The explanations, worked examples and practice items were written by Ganzaa.

1Writing a reference to another sheet

2The easy way: point and click

Worked example: Uwase's year total

3What happens when sheets change

Practice

Writing a reference to another sheet

The easy way: point and click

What happens when sheets change