Unit 2: Electronics Spreadsheet (Advanced) using LibreOffice Calc
Chapter 6. Linking Spreadsheet Data
Check Your Progress
A. Multiple-choice questions.
Question 1:
Insert Sheet dialog can be invoked from _______.
(a) sheet
(b) insert
(c) tools
(d) Windows
Answer: (a) sheet
Question 2:
_______ refers to cell G5 of sheet named My Sheet.
(a) $My Sheet.’G5’
(b) $My Sheet_’G5’
(c) $ ‘MySheet’.G5
(d) $ ‘MySheet’_G5
Answer: (c) $ ‘MySheet’.G5
Question 3:
The path of a file has ________ forward slashes.
(a) four
(b) three
(c) two
(d) one
Answer: (b) three
Question 4:
Which of the following features is used to jump to a different spreadsheet from the current spreadsheet in LibreOffice Calc?
(a) Macro
(b) Hyperlink
(c) connect
(d) Copy
Answer: (b) Hyperlink
B. Fill in the blanks.
Question 1:
A relative hyperlink stores the location with respect to the _______ location.
Answer: current
Question 2:
While inserting tables from a webpage _________ selects the entire HTML document.
Answer: HTML_all
Question 3:
The extension of LibreOffice base is _______.
Answer: .odb.
Question 4:
_________ are used to enclose sheet names as there might be a space within sheet names.
Answer: Single quotes (‘ ’)
Question 5:
The From file option of _______ Dialog box allows to insert sheet from another file.
Answer: Insert Sheet
C. State whether the following statements are True or False.
Question 1:
A sheet can only be added before the current sheet.
Answer: False
Question 2:
If ‘sales’ sheet has a reference to ‘cost’ sheet then any changes made to ‘cost’ sheet will be reflected in the sales sheet as well.
Answer: True
Question 3:
It is not possible to link a sheet as a reference in another sheet.
Answer: False
Question 4:
We can insert data from a table created on a web page into a spreadsheet.
Answer: True
Question 5:
A hyperlink once created on a sheet cannot be deleted.
Answer: False
D. Answer the following questions.
Question 1:
Name the two ways to link the sheets in a LibreOffice Calc.
Answer: Linking a sheet can be done in two ways,
- (i) Creating reference to other sheets by using a keyboard and mouse, and
- (ii) By linking external data.
Question 2:
Differentiate between Relative and Absolute Hyperlink.
Answer: A hyperlink can be either absolute or relative.
| Absolute Hyperlink | Relative Hyperlink |
|---|---|
| An absolute hyperlink stores the complete location where the file is stored. | A relative hyperlink stores the location with respect to the current location. |
| If the file is removed from the location, absolute hyperlink will not work. | 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. |
| For example: C:\Users\ADMIN\Downloads\try.ods is an absolute link as it defines the complete path of the file. | For example: Admin\Downloads\try.ods is a relative hyperlink as it is dependent on the current location and thus the folder admin is searched where the active spreadsheet is being stored. |
Question 3:
Write steps to extract a table from a web page in a spreadsheet.
Answer: The versatility of a spreadsheet allows us to insert/extract tables from HTML documents into LibreOffice Calc.
To insert the tables from an HTML document, we can use the External
Data Dialog box.
The steps for extracting / inserting a table from a web page in a spreadsheet:
- Step 1: Open the spreadsheet where external data is to be inserted.
- Step 2: Select the cell to store the first cell of the table in the external data.
- Step 3: Select Sheet > Link to External Data.
- Step 4: The External Data dialog box is displayed. Type the URL of the source document or select it from the drop-down list if it is listed and press enter.
- Step 5: A dialog box is displayed to select the language for import. Selecting Automatic shows data in the same language as on the webpage.
- Step 6: From the Available Tables/Ranges list, choose the desired table. If you choose HTML_all option, then the entire HTML document is selected.
- Step 7: Click on OK. The selected table will be inserted in the spreadsheet.
Question 4:
Write steps to register a data source that is in *.odb format.
Answer: The extension of LibreOffice Base file is .odb, stands for open database.
To register a data source that is in *.odb format, follow the steps
given below:
- Step 1: Select Tools > Options > LibreOffice Base > Databases. The Options – LibreOffice Base-Databases dialog box appears.
- Step 2: Click the New button to open the Create Database Link dialog box
- Step 3: Enter the location of the database file, or click Browse to open a file browser and select the database file.
- Step 4: Type a name to use as the registered name for the database and click OK. The database is added to the list of registered databases.
Note – The OK button is enabled only when both fields are filled in
Question 5:
State advantages of extracting data from a web page into a spreadsheet.
Answer: As we know, The Internet is a rich source of information, which is stored in the form of web pages. The web pages are written in HTML documents. Data on a web page can be stored in the form of tables.
Extracting data from a web page gives the facility to use the same tables, available in the webpage, in our spreadsheet file. We save our time and use the authentic data for our purposes.
Lab Exercises (Do by yourself in Lab)
- Create three sheets named Jan, Feb and March. In each sheet store the attendance of employees for a month. Apply the count function to count the number of days the employees were present by counting P.
- Add another sheet named ‘Consolidated Attendance’ that stores total attendance of three months and calculates the percentage.
- Create a spreadsheet containing the list of Prime ministers of India with their tenure from the website https://knowindia.gov.in/general-information/prime-ministersof-india.php. Perform steps to extract the table from the website into a Calc Sheet and display the count of Prime
Ministers of India till date.
