Part B — Electronic Spreadsheet (Advanced)

In a spreadsheet, data may be distributed among different sheets or even different spreadsheet files.

LibreOffice Calc provides features that allow us to create multiple sheets and link data between them.

Linking is useful when information stored at different places needs to be connected or reused.

A spreadsheet document can contain multiple worksheets.

Different sheets can be used to store different categories of information.

Examples

• Student information in one sheet.
• Marks in another sheet.
• Attendance in another sheet.
• Summary or result in another sheet.

A new sheet can be inserted into a spreadsheet document using the Insert Sheet dialog.

Important Options

• Insert a new sheet.
• Specify the number of sheets.
• Give suitable names to sheets.
• Insert sheets from another file.

LibreOffice Calc allows a sheet from another spreadsheet file to be inserted into the current spreadsheet.

From File Option

The From file option in the Insert Sheet dialog allows a sheet to be inserted from another file.

A cell in one sheet can refer to a cell in another sheet of the same spreadsheet document.

This is called creating a reference to another sheet.

The linked value is obtained from the referenced cell. If the value in the original cell changes, the referenced value can also be updated.

A reference to another sheet can be created using the keyboard or mouse.

General Method

1. Select the cell where the result/reference is required.
2. Type the reference formula or begin entering the required reference.
3. Select the required sheet.
4. Select the required cell.
5. Complete the reference and press Enter.

LibreOffice Calc uses a specific syntax for referring to a cell located on another sheet.

If the sheet name contains spaces, the sheet name is enclosed in single quotation marks.

Here:

• My Sheet = name of the sheet.
• G5 = cell reference.

References in a spreadsheet can behave differently when copied from one location to another.

Relative Reference

A relative reference changes according to the new position when the formula is copied.

Absolute Reference

An absolute reference remains fixed when the formula is copied.

LibreOffice Calc also allows data to be linked from a different spreadsheet document.

This is useful when information is stored in separate spreadsheet files but needs to be displayed or used in the current spreadsheet.

Basic Concept

The external spreadsheet can act as the source of the linked information.

When a spreadsheet refers to an external file, the location of the file is important.

A file path identifies the location of a file.

Example Structure

The exact path depends on the location of the external spreadsheet file.

A hyperlink provides a way to move from one location to another.

In Calc, a hyperlink can be used to jump to a different spreadsheet from the current spreadsheet.

A relative hyperlink stores the location of a file with respect to the current location.

An absolute hyperlink stores the complete location of the target.

Unlike a relative hyperlink, it does not depend on the current location.

LibreOffice Calc provides a feature for obtaining data from an external source.

External data may be obtained from sources such as a webpage.

The imported information can then be inserted into the spreadsheet.

Calc can insert a table from a webpage into a spreadsheet.

Basic Procedure

1. Open the required external-data/import feature.
2. Type the URL of the source document.
3. If the URL is already listed, select it from the drop-down list.
4. Press Enter.
5. A dialog box appears for selecting the language for import.
6. Select the appropriate language option.
7. Choose the required table/range from Available Tables/Ranges.
8. Insert the selected table into the spreadsheet.

After connecting to a webpage, Calc displays available tables or ranges from the webpage.

The required table can be selected from the Available Tables/Ranges list.

HTML_all

Instead of selecting the complete webpage, a specific table can be selected from the Available Tables/Ranges list.

For example, if a webpage contains several HTML tables, a particular table such as HTML_10 can be selected.

LibreOffice Calc can link spreadsheet documents with databases and other data sources.

The data source must be registered with LibreOffice before it can be used in this manner.

Meaning of Registration

Registration informs LibreOffice about:

• The type of data source.
• The location of the data source/file.

LibreOffice Base is used as the database source for the example of registered data sources.

To register a database in LibreOffice, the chapter gives the following procedure.

1. Select Tools → Options.
2. Select LibreOffice Base → Databases.
3. The Options — LibreOffice Base-Databases dialog box appears.
4. Click the New button.
5. The Create Database Link dialog box opens.

• The chapter is Linking Spreadsheet Data.
• The Insert Sheet dialog is invoked from the Sheet menu.
• From file allows insertion of a sheet from another file.
• A cell can refer to a cell on another sheet.
• Sheet names containing spaces are enclosed in single quotation marks.
• Hyperlink is used to jump to a different spreadsheet.
• A relative hyperlink stores location with respect to the current location.
• A file path uses forward slashes.
• While importing webpage data, HTML_all selects the entire HTML document.
• Automatic language selection displays webpage data in the same language as the webpage.
• Calc can link with registered databases and other data sources.
• A data source must be registered with LibreOffice.
• The chapter uses LibreOffice Base as the registered database example.
• LibreOffice Base database extension is .odb.
• Database registration uses Tools → Options → LibreOffice Base → Databases .