Welcome to the spoken tutorial on Linking Calc Data.
In this tutorial, we will learn:
How to reference other sheets and Insert hyperlinks in Calc
This tutorial is recorded using Ubuntu Linux OS version 18.04 and LibreOffice Suite version 6.3.5
LibreOffice Calc allows you to reference A cell from sheet to a cell in another sheet
A cell from another spreadsheet
Let’s open Personal-Finance-Tracker.ods.
This file has been provided to you in the Code files link on this tutorial page. Please download and extract the file.
Make its copy and use it for practising.
Now, let us find the sum total of the components under Spent and Received.
Click on the cell C8.
Click on the little black box that is visible at the bottom-right corner of the cell.
Drag it till cell E8 and release the mouse button.
Now, we will display the total balance under Cost and Spent on a different sheet.
We will do this using cell referencing.
Let’s go to Sheet 3.
Now click on cell A1 and type the heading as COMPONENT inside it.
Click on cell B1 and type the heading BALANCE inside it.
Now, let’s type the names of the components under this heading.
Click on the cell A3 and type COST and press Enter.
Below COST, let’s type the next component as SPENT in cell A4.
Now, click on the empty cell B3.
The cells B3 and B4 will have the total balance under the headings COST and SPENT.
It has already been calculated in Sheet 1. This will be done by referencing.
Let’s make the cell reference in cell B3.
Click on the equal to sign named Formula next to the Input line.
Now, on the Sheet tab click on Sheet 1.
In this sheet, we will click on cell C8 which contains the total for Cost.
Notice that in the Input line, the statement $Sheet 1 dot C8, is displayed.
Now click on the check mark named Accept next to the Input line.
Notice that we redirected back to Sheet 3.
And the grand total in Sheet 1 is automatically filled in cell B3 of Sheet 3.
Similarly, we can enter the grand totals of other components through referencing.
Referencing is very useful to summarise data if there are many sheets.
Now, let’s learn how to insert Hyperlinks in Calc sheets.
Hyperlink within documents are words or phrases that can be clicked.
On clicking them, we jump to a new document or a new section within the current document or Even to a webpage.
In this file, our main data is in Sheet 1 and the rest of the content in Sheet 3.
Let’s say we want to jump from Sheet 1 to Sheet 3.
First, click on the Sheet 1 tab.
Here let’s click on cell B10 and type Sheet 3.
You see that the name Sheet 3 is displayed on the Input line.
Now select the text Sheet 3 in the Input Line.
After selecting the text, click on the Insert Hyperlink icon in the Standard toolbar.
The Hyperlink dialog box appears.
On the left side, click on Document.
Under Target in Document section, click on the circle icon to the right.
The Target in Document list box opens.
Expand Sheets by clicking on the arrow and select Sheet 3.
Click on the Apply button and then on Close button.
In the Hyperlink dialog box, in the Target field we see the text Sheet 3.
We also see that the path of Sheet 3 gets inserted in the URL field.
At the bottom, click on the Apply button first and then on the Close button.
Now the text Sheet 3 is highlighted in the cell.
Click anywhere outside to deselect the cell.
Keep the cursor on the word Sheet 3. And press the Ctrl key and the left mouse-button together.
We have been redirected to Sheet 3.
Let us go back to Sheet 1.
To remove the hyperlink, first click on the cell B10 in Sheet 1
Right-click on the cell and select Clear Direct Formatting from the context menu.
Alternatively, we can click on the Clear Direct Formatting icon in the Standard Toolbar.
The text is no longer hyperlinked. It is just like any normal text in the spreadsheet.
Next we will learn how to create a hyperlink to an external file.
For this demonstration, I will be using the file Contact-Details.ods
The same is available in the Code Files link.
Please download and save it in the folder where you have saved Personal-Finance-Tracker.ods
Let us go back to Personal-Finance-Tracker.ods
Click on cell B10 and type Contact Details.
Now select the text Contact Details in the Input Line.
After selecting the text, click on the Insert Hyperlink icon in the Standard toolbar.
The Hyperlink dialog box appears.
To create a hyperlink to another spreadsheet or document, click on the Document option.
Click on the folder icon on the right of the Path field.
Select Desktop location. Scroll down and locate the new spreadsheet Contact-Details.ods which we had saved.
Now, double-click on the “Contact-Details.ods” file.
We see that the path to the file gets inserted in the Path field.
At the bottom, click on the Apply button first and then on the Close button.
Now the text Contact Details is highlighted in the cell.
Click anywhere outside to deselect the cell.
We see that the text "Contact Details" is blue in color.
Which means the text is now a hyperlink.
Keep the cursor on the word Contact Details. And press the Ctrl key and the left mouse-button together.
We see that the file Contact Details.ods opens up.
Click on the X icon at the top right corner to close this file.
Let us undo these changes.
Hyperlinking to a web page is similar.
Click on cell B10 and type Spoken
Now select the text Spoken in the Input Line.
After selecting the text, click on the Insert Hyperlink icon in the Standard toolbar.
The Hyperlink dialog box appears.
In the Hyperlink dialog box, by default, the Internet section is selected in the left pane.
In the Hyperlink Type section, the Web radio button is also selected.
Now in the URL field, type https://spoken-tutorial.org
At the bottom, click on the Apply button and then on the Close button.
Click anywhere outside to deselect the cell.
Observe that the text Spoken becomes a hyperlinked text.
For the next part of this demonstration, you will need internet connectivity. If you don’t have it, then skip this part.
By holding the Ctrl key click on the hyperlinked text.
We will be redirected to the relevant URL.
So, these are the different methods to create hyperlinks in Calc.
This brings us to the end of this tutorial. Let us summarize.
In this tutorial, we learnt to: Add reference to other sheets and Insert hyperlinks in Calc.
As an assignment Open Spreadsheet-Practice.ods file
Find out the sum total of salary in Sheet 1.
Use sum total to display it on a different sheet using cell referencing.
Use the Hyperlink icon and link LibreOffice website
The video at the following link summarises the Spoken Tutorial project. Please download and watch it
We conduct workshops using Spoken Tutorials and give certificates. For more details, please write to us.
Please post your timed queries in this forum.
Spoken Tutorial project is funded by MHRD, Govt. of India.
This tutorial was originally contributed by DesiCrew Solutions Pvt. Ltd. in 2011
This is Arvind along with Spoken Tutorial team from IIT Bombay signing off. Thank you for watching.