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

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

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