LibreOffice-Suite-Calc-6.3/C3/Linking-Calc-Data/English-timed

From Script | Spoken-Tutorial
Jump to: navigation, search


TIME NARRATION
00:01 Welcome to the spoken tutorial on Linking Calc Data.
00:06 In this tutorial, we will learn:
00:09 How to reference other sheets and Insert hyperlinks in Calc
00:16 This tutorial is recorded using

Ubuntu Linux OS version 18.04 and LibreOffice Suite version 6.3.5

00:29 LibreOffice Calc allows you to reference

A cell from sheet to a cell in another sheet

00:36 A cell from another spreadsheet
00:39 Let’s open Personal Finance Tracker dot ods.
00:44 This file has been provided to you in the Code files link on this tutorial page.

Please download and extract the file.

00:55 Make its copy and use it for practising.
00:59 Now, let us find the sum total of the components under Spent and Received.
01:07 Click on the cell C8.
01:10 Click on the little black box that is visible at the bottom-right corner of the cell.
01:17 Drag it till cell E8 and release the mouse button.
01:22 Now, we will display the total balance under Cost and Spent on a different sheet.
01:30 We will do this using cell referencing.
01:34 Let’s go to Sheet 3.
01:37 Now click on cell A1 and type the heading as COMPONENT inside it.
01:45 Click on cell B1 and type the heading BALANCE inside it.
01:51 Now, let’s type the names of the components under this heading.
01:57 Click on the cell A3 and type COST and press Enter.
02:04 Below COST, let’s type the next component as SPENT in cell A4.
02:11 Now, click on the empty cell B3.
02:16 The cells B3 and B4 will have the total balance under the headings COST and SPENT.
02:25 It has already been calculated in Sheet 1.

This will be done by referencing.

02:33 Let’s make the cell reference in cell B3.
02:38 Click on the equal to sign named Formula next to the Input line.
02:44 Now, on the Sheet tab click on Sheet 1.
02:50 In this sheet, we will click on cell C8 which contains the total for Cost.
02:57 Notice that in the Input line, the statement $Sheet 1 dot C8, is displayed.
03:06 Now click on the check mark named Accept next to the Input line.
03:12 Notice that we redirected back to Sheet 3.
03:17 And the grand total in Sheet 1 is automatically filled in cell B3 of Sheet 3.
03:25 Similarly, we can enter the grand totals of other components through referencing.
03:35 Referencing is very useful to summarise data if there are many sheets.
03:42 Now, let’s learn how to insert Hyperlinks in Calc sheets.
03:48 Hyperlink within documents are words or phrases that can be clicked.
03:55 On clicking them, we jump to

a new document or a new section within the current document or Even to a webpage.

04:08 In this file, our main data is in Sheet 1 and the rest of the content in Sheet 3.
04:16 Let’s say we want to jump from Sheet 1 to Sheet 3.
04:21 First, click on the Sheet 1 tab.
04:25 Here let’s click on cell B10 and type Sheet 3.
04:31 You see that the name Sheet 3 is displayed on the Input line.
04:37 Now select the text Sheet 3 in the Input Line.
04:42 After selecting the text, click on the Insert Hyperlink icon in the Standard toolbar.
04:50 The Hyperlink dialog box appears.
04:54 On the left side, click on Document.
04:58 Under Target in Document section, click on the circle icon to the right.
05:05 The Target in Document list box opens.
05:09 Expand Sheets by clicking on the arrow and select Sheet 3.
05:15 Click on the Apply button and then on Close button.
05:20 In the Hyperlink dialog box, in the Target field we see the text Sheet 3.
05:26 We also see that the path of Sheet 3 gets inserted in the URL field.
05:33 At the bottom, click on the Apply button first and then on the Close button.
05:39 Now the text Sheet 3 is highlighted in the cell.
05:44 Click anywhere outside to deselect the cell.
05:48 Keep the cursor on the word Sheet 3.

And press the Ctrl key and the left mouse-button together.

05:57 We have been redirected to Sheet 3.
06:01 Let us go back to Sheet 1.
06:04 To remove the hyperlink, first click on the cell B10 in Sheet 1
06:10 Right-click on the cell and select Clear Direct Formatting from the context menu.
06:17 Alternatively, we can click on the Clear Direct Formatting icon in the Standard Toolbar.
06:24 The text is no longer hyperlinked.

It is just like any normal text in the spreadsheet.

06:32 Next we will learn how to create a hyperlink to an external file.
06:38 For this demonstration, I will be using the file Contact hyphen Details dot ods
06:46 The same is available in the Code Files link.
06:50 Please download and save it in the folder where you have saved Personal Finance Tracker dot ods
06:58 Let us go back to Personal Finance Tracker dot ods
07:03 Click on cell B10 and type Contact Details.
07:08 Now select the text Contact Details in the Input Line.
07:13 After selecting the text, click on the Insert Hyperlink icon in the Standard toolbar.
07:20 The Hyperlink dialog box appears.
07:24 To create a hyperlink to another spreadsheet or document, click on the Document option.
07:31 Click on the folder icon on the right of the Path field.
07:36 Select Desktop location.

Scroll down and locate the new spreadsheet Contact Details dot ods which we had saved.

07:46 Now, double-click on the “Contact Details dot ods” file.
07:51 We see that the path to the file gets inserted in the Path field.
07:57 At the bottom, click on the Apply button first and then on the Close button.
08:03 Now the text Contact Details is highlighted in the cell.
08:08 Click anywhere outside to deselect the cell.
08:12 We see that the text "Contact Details" is blue in color.
08:18 Which means the text is now a hyperlink.
08:22 Keep the cursor on the word Contact Details.

And press the Ctrl key and the left mouse-button together.

08:31 We see that the file Contact Details dot ods opens up.
08:36 Click on the X icon at the top right corner to close this file.
08:41 Let us undo these changes.
08:44 Hyperlinking to a web page is similar.
08:48 Click on cell B10 and type Spoken
08:52 Now select the text Spoken in the Input Line.
08:57 After selecting the text, click on the Insert Hyperlink icon in the Standard toolbar.
09:04 The Hyperlink dialog box appears.
09:08 In the Hyperlink dialog box, by default, the Internet section is selected in the left pane.
09:15 In the Hyperlink Type section, the Web radio button is also selected.
09:12 Now in the URL field, type https://spoken-tutorial.org
09:31 At the bottom, click on the Apply button and then on the Close button.
09:37 Click anywhere outside to deselect the cell.
09:41 Observe that the text Spoken becomes a hyperlinked text.
09:47 For the next part of this demonstration, you will need internet connectivity.

If you don’t have it, then skip this part.

09:56 By holding the Ctrl key click on the hyperlinked text.
10:01 We will be redirected to the relevant URL.
10:05 So, these are the different methods to create hyperlinks in Calc.
10:10 This brings us to the end of this tutorial.

Let us summarize.

10:15 In this tutorial, we learnt to:

Add reference to other sheets and Insert hyperlinks in Calc.

10:25 As an assignment

Open Spreadsheet hyphen Practice dot ods file

10:31 Find out the sum total of salary in Sheet 1.
10:36 Use sum total to display it on a different sheet using cell referencing.
10:42 Use the Hyperlink icon and link LibreOffice website
10:48 The video at the following link summarises the Spoken Tutorial project.

Please download and watch it

10:56 We conduct workshops using Spoken Tutorials and give certificates. For more details, please write to us.
11:06 Please post your timed queries in this forum.
11:10 Spoken Tutorial project is funded by MHRD, Govt. of India.
11:16 This tutorial was originally contributed by DesiCrew Solutions Pvt. Ltd. in 2011
11:24 This is Arvind along with Spoken Tutorial team from IIT Bombay signing off.

Thank you for watching.

Contributors and Content Editors

PoojaMoolya