Ch 6 Linking Spreadsheet Data Notes

Introduction
When the marks scored by each student in three terminal examinations are stored in three different
sheets T1, T2, T3. Now to generate final result in single result sheet by finding the addition of marks of
each subject, what would be the ideal solution?

Retyping or copying the marks can be one solution but it will be time consuming and also there are
chances of committing typing errors. Instead, the ideal solution will be to find a way to refer the marks
stored in the sheets T1, T2 and T3.

In this manner, there are nearly no chances of errors and if the marks of a subject are changed, they will
be automatically reflected in the final sheet.

Inserting New Sheet in Spreadsheet

In LibreOffice Calc, by default there is only one sheet but we can insert multiple sheets. There are three
ways to insert new sheet.

1. To add a new sheet in the spreadsheet, click on the Add new sheet icon(+) sign located on the Sheet tab
of the spreadsheet

2. Right click anywhere on the sheet tab and select Insert sheet option from the drop-down list. Insert
Sheet dialog box will open as shown below. It gives us a choice to put the new sheet, after current sheet
or before current sheet or assign the name of the sheet etc.

Linking Spreadsheet data

3. Click on Sheet>insert sheet to open Insert sheet dialog box which will help us to insert a new sheet.
Creating Reference to Other Sheets by Using Keyboard and Mouse
In this section we will learn how to reference cells in other sheets using mouse and keyboard. Let we
understand this by doing the following practical.

Creating reference using Mouse

Create the ‘Sheet1’ and ‘Sheet2’ in LibreOffice Calc as shown below:

Linking Spreadsheet data

Now we want the total marks in another Sheet named ‘Result’

To calculate the final marks for English in ‘Result’ sheet, follow the following steps.

1. First copy the Student Name and subject Name from ‘Sheet1’ to ‘Result’ sheet.

2. Type =SUM() in a cell and click between the brackets.

3. Now click on the ‘Sheet1’ sheet and click the English Marks for the first student and write (+) for the
next value. Now click on the ‘Sheet2’ sheet and click the English Marks for the first student as shown
below

Linking Spreadsheet data

4. Press Enter key, the total marks of subject English will be displayed in cell B2 of ‘Result’ sheet.

5. Then use fill handle to fill the cells up to the last student’s data. You can copy the same formula for
other subjects

NOTE: Any changes made to marks in ‘Sheet1’ and ‘Sheet2’ sheet will be reflected in the ‘result’ sheet as
well. That is how the sheets are linked together

Creating reference using Keyboard

To refer to a cell in another sheet precede the cell reference with a ‘$’ sign. It is then followed by the name
of the sheet in ‘ ’ (single quotes) followed by a . (dot) and then the cell address. For example, to refer a cell
B2 of sheet named Sheet1 we will type: $‛Sheet1’.B2

In above practical, we can directly type the following formula in cell B2 of Sheet named ‘Result’ and then
drag the formula to calculate the sum of the marks
=SUM($’Sheet1′.B2 + $’Sheet2′.B2)

OR

=SUM($Sheet1.B2 + $Sheet2.B2)

NOTE: Single quotes (‘ ’) are mandatory if there is a space in the Sheet name like ‘Sheet 1’.

NOTE: To refer to a cell in a different spreadsheet we write in single quotes the path of the file followed
by #$ then the name of the sheet followed by a . (dot) and then the cell address.
For example: ‘[Link]

The path of a file has three forward slash ///. A filename can have space within its name hence single
quotes (‘ ‘) are used. It is also possible to insert a sheet from another file. The From file option of Insert
Sheet Dialog box allows us to insert sheet from another file as well.

Hyperlinks to the Sheet

Hyperlinks can be used in Calc to jump to a different location from within a spreadsheet to other parts of
the same file or to different files or even to web sites.

Relative and Absolute Hyperlinks

A hyperlink can be either absolute or relative. An absolute hyperlink stores the complete location where
the file is stored. So, if the file is removed from the location, absolute hyperlink will not work. For
example: C:\Users\ADMIN\Downloads\[Link] is an absolute link as it defines the complete path of the
file.

A relative hyperlink stores the location with respect to the current location. For example:
Admin\Downloads\[Link] is a relative hyperlink as it is dependent on the current location. If the
complete folder containing the active spreadsheet is moved the relative link will still be accessible as it is
bound to the source folder where the active spreadsheet is stored.

Creating Hyperlinks
Suppose, you have to hyperlink a “Sheet1” of “Result-X-A” spreadsheet document in the “Result-X-B”
spreadsheet document, then follow the following steps:

1. Open the “Result-X-B” spreadsheet document.

2. Click on Insert > Hyperlink. An Hyperlink dialog box will open.

 Hyperlink – Linking Spreadsheet Data

3. Click on the Document on the left pan of dialog box. Click on the button located after the Path. Select the
document “Result-X-A”

4. Then click on the Target button to choose the sheet which is to be hyperlinked. Here in our case we will
select the sheet “Sheet1” as shown above.

5. Click on Apply and Close button.

6. Enter the text in the Text box to assign the hyperlink to that text.

7. Click on Apply and Close button.

NOTE: To open the hyperlinked sheet, press the Ctrl key and click on the hyperlinked word “ResultX-A”,
the sheet will be opened in the new window

Editing Hyperlinks
To edit an existing link, place the cursor anywhere in the link. Right click and choose Edit Hyperlink…, the
Hyperlink dialog box will be displayed, where we can do required changes in the hyperlink.

On clicking the Remove Hyperlink option, the link will be removed from the text.

Linking to External Data

Internet is a rich source of information, which is stored in the form of web pages. The versatility of a
spreadsheet allows us to insert tables from HTML documents into Calc. The steps for the same are given
below
1. Open the spreadsheet where external data is to be inserted.

2. Select Sheet > External Links…

3. The External Data dialog box will open.

Linking Spreadsheet Data

4. Type the URL of the source document and press enter.

5. A dialog box is displayed to select the language for import. Selecting Automatic shows data in the same
language as in the webpage.

Linking Spreadsheet Data

6. From the Available Tables/Ranges list, choose the desired table and click OK. (as shown below)
 Inserting table from webpage
7. Table will be inserted in the spreadsheet

NOTE: If you choose HTML_all option, then the entire HTML document is selected.

Linking to Registered Data Sources

LibreOffice Calc allows us to link spreadsheet documents with databases and other data sources. The
data source needs to be registered with LibreOffice.

The extension of LibreOffice Base is .odb. To register a data source that is in *.odb format, follow the steps
given below.

1. Select Tools > Options > LibreOffice Base > Databases. The Options – LibreOffice Base-Databases dialog
box appears.

2. Click the New button to open the Create Database Link dialog box.

3. Click Browse to open a file browser and select the database file.

4. Type a name to use as the registered name for the database and click OK.
Linking Spreadsheet Data