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

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