Nancyvarkey at 21:15, 10 September 2020

2020-09-10T21:15:53Z

Arvindpillai90: Created page with "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..."

2020-09-10T08:49:20Z

Created page with "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..."

New page

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

{| border="1"
|-
|| '''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 '''<nowiki>=</nowiki>''' 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 spread'''sheet'''.
|-
|| 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.
|-
|}

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

Nancyvarkey at 21:15, 10 September 2020

Arvindpillai90: Created page with "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..."