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

Title: Linking Calc Data

Author: Arvind Pillai

Novice reviewer: Nikita Misal and Pooja Molya

Domain reviewer: Praveen S

Keywords: LibreOffice,Calc,Ubuntu,Use reference in Calc, Use Hyperlinks in Calc

VISUAL CUE	NARRATION
Slide: Title	Welcome to the spoken tutorial on Linking Calc Data.
Slide: Learning Objectives	In this tutorial, we will learn: How to reference other sheets and Insert hyperlinks in Calc
Slide: System Requirements	This tutorial is recorded using Ubuntu Linux OS version 18.04 and LibreOffice Suite version 6.3.5
Slide:Reference Cells	LibreOffice Calc allows you to reference A cell from sheet to a cell in another sheet A cell from another spreadsheet
Open Personal-Finance-Tracker.ods.	Let’s open Personal-Finance-Tracker.ods.
Slide: Code files	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.
Point to Cost and Spent	Now, let us find the sum total of the components under Spent and Received.
Click on cell C8	Click on the cell C8.
Click on the little black box at the bottom-right corner of the cell Drag it till cell E8 and release the mouse button.	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.
Point to the total balance under Cost and Spent	Now, we will display the total balance under Cost and Spent on a different sheet. We will do this using cell referencing.
Click on Sheet 3	Let’s go to Sheet 3.
Click on cell A1. Type COMPONENT inside it.	Now click on cell A1 and type the heading as COMPONENT inside it.
Click on cell B1 Type BALANCE inside it.	Click on cell B1 and type the heading BALANCE inside it.
Narration Only	Now, let’s type the names of the components under this heading.
Click on cell A3 Type COSTS >> press Enter	Click on the cell A3 and type COST and press Enter.
Click on cell A4 Type Spent	Below COST, let’s type the next component as SPENT in cell A4.
Click on cell E3	Now, click on the empty cell B3.
Point to the cells B3 and B4 Point to the text Cost in A3 and Spent in A4 Point to Sheet1 tab at the bottom.	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.
Click on = next to the input line.	Let’s make the cell reference in cell B3. Click on the equal to sign named Formula next to the Input line.
Click on Sheet 1 tab.	Now, on the Sheet tab click on Sheet 1.
Click on cell C8 which has the total for Cost	In this sheet, we will click on cell C8 which contains the total for Cost.
Point to input line	Notice that in the Input line, the statement $Sheet 1 dot C8, is displayed.
Click on Checkmark next to the input line.	Now click on the check mark named Accept next to the Input line.
Point to the cell B3 which appears in the Sheet 3.	Notice that we redirected back to Sheet 3. And the grand total in Sheet 1 is automatically filled in cell B3 of Sheet 3.
Show demo of Spent Sum total referencing.	Similarly,we can enter the grand totals of other components through referencing.
Click on Sheet 1 then back on Sheet 3	Referencing is very useful to summarise data if there are many sheets.
Only Narration	Now, let’s learn how to insert Hyperlinks in Calc sheets.
Slide: What is a Hyperlink?	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.
Only Narration Point to Sheet 1 and then to Sheet 3.	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.
Click on Sheet 1	First, click on the Sheet 1 tab.
Type Sheet 3 in B10	Here let’s click on cell B10 and type Sheet 3.
Point to Input line	You see that the name Sheet 3 is displayed on the Input line.
Select text Sheet 3 in the input line.	Now select the text Sheet 3 in the Input Line.
Click on the Insert Hyperlink icon on the Standard toolbar.	After selecting the text, click on the Insert Hyperlink icon in the Standard toolbar.
Point to the Hyperlink dialog box.	The Hyperlink dialog box appears.
Click on Document	On the left side, click on Document.
Point to Target in 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 >> select Sheet 3 Click on Apply and Close button	Expand Sheets by clicking on the arrow and select Sheet 3. Click on the Apply button and then on Close button.
Point to Target field >> Sheet 3.	In the Hyperlink dialog box, in the Target field we see the text Sheet 3.
Point on the URL field	We also see that the path of Sheet 3 gets inserted in the URL field.
Click on Apply >> click on Close button	At the bottom, click on the Apply button first and then on the Close button.
Point to highlighted text Sheet 3	Now the text Sheet 3 is highlighted in the cell.
Click on any random cell	Click anywhere outside to deselect the cell.
Cursor on Sheet 3 Press Ctrl+left key mouse	Keep the cursor on the word Sheet 3. And press the Ctrl key and the left mouse-button together.
Highlight with a red box	We have been redirected to Sheet 3.
Click on the Sheet 1 tab	Let us go back to Sheet 1.
Click on B10 in Sheet 1	To remove the hyperlink, first click on the cell B10 in Sheet 1
Right click and Point on Clear Direct Formatting	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.
Point to the text which no longer is a hyperlink.	The text is no longer hyperlinked. It is just like any normal text in the spreadsheet.
Only narration	Next we will learn how to create a hyperlink to an external file.
Show to Contact-Details.ods in file browser	For this demonstration, I will be using the file Contact-Details.ods The same is available in the Code Files link. Pls download and save it in the folder where you have saved Personal-Finance-Tracker.ods
Switch to Personal-Finance-Tracker.ods	Let us go back to Personal-Finance-Tracker.ods
Type Contact Details in B10	Click on cell B10 and type Contact Details.
Select text Contact Details in the input line.	Now select the text Contact Details in the Input Line.
Click on the Insert Hyperlink icon on the Standard toolbar.	After selecting the text, click on the Insert Hyperlink icon in the Standard toolbar.
Point to the Hyperlink dialog box.	The Hyperlink dialog box appears.
Click on Document option	To create a hyperlink to another spreadsheet or document, click on the Document option.
Click on the folder icon on the right of Path field	Click on the folder icon on the right of the Path field.
Click on Desktop >> Scroll down & locate Contact-Details.ods	Select Desktop location. Scroll down and locate the new spreadsheet Contact-Details.ods which we had saved.
Select Contact-Details.ods and click on Open button	Now, double-click on the “Contact-Details.ods” file.
Point on the Path field	We see that the path to the file gets inserted in the Path field.
Click on Apply >> click on Close button	At the bottom, click on the Apply button first and then on the Close button.
Point to highlighted text Contact Details	Now the text Contact Details is highlighted in the cell.
Click on any random cell	Click anywhere outside to deselect the cell.
Point to Contact Details	We see that the text "Contact Details" is blue in color. Which means the text is now a hyperlink.
Cursor on Contact Details Press Ctrl+left key mouse	Keep the cursor on the word Contact Details. And press the Ctrl key and the left mouse-button together.
Point to Contact Details.ods	We see that the file Contact Details.ods opens up.
Click on X icon	Click on the X icon at the top right corner to close this file.
Undo the changes.	Let us undo these changes.
Only narration.	Hyperlinking to a web page is similar.
Type Spoken in B10	Click on cell B10 and type Spoken
Select text Spoken in the input line.	Now select the text Spoken in the Input Line.
Click on the Insert Hyperlink icon on the Standard toolbar.	After selecting the text, click on the Insert Hyperlink icon in the Standard toolbar.
Point to the Hyperlink dialog box.	The Hyperlink dialog box appears.
Go to the left pane >> Point to the Internet. Go to the Hyperlink Type >> Point to Web.	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.
Type https://spoken-tutorial.org in the URL field. Click on the Apply >> Close.	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 on any random cell	Click anywhere outside to deselect the cell.
Point to hyperlinked text.	Observe that the text Spoken becomes a hyperlinked text.
Only Narration	For the next part of this demonstration, you will need internet connectivity. If you don’t have it, then skip this part.
Press Ctrl + click on hyperlinked text. Point to website page	By holding the Ctrl key click on the hyperlinked text. We will be redirected to the relevant URL. <<PAUSE>>
Only narration.	So, these are the different methods to create hyperlinks in Calc.
Narration:	This brings us to the end of this tutorial. Let us summarize.
Slide: Summary	In this tutorial, we learnt to: Add reference to other sheets and Insert hyperlinks in Calc.
Slide:Assignment	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 https://www.libreoffice.org/
Slide: About Spoken Tutorial Project	The video at the following link summarises the Spoken Tutorial project.y Please download and watch it
Slide: Spoken tutorial workshops	We conduct workshops using Spoken Tutorials and give certificates. For more details, please write to us.
Slide: Forums	Pls post your timed queries in this forum.
Slide: Acknowledgement	Spoken Tutorial project is funded by MHRD, Govt. of India.
Slide: Thank you Acknowledgement to DesiCrew	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.

Contributors and Content Editors

Arvindpillai90, Nancyvarkey