06 Linking Speadaheet Data
Linking spreadsheet data enables you to keep information updated without editing multiple locations
every time the data changes. With the ability to create links, you do not need to enter or update the same
data in multiple sheets. This saves time, reduces errors, and improves data accuracy and integrity. In this
chapter, you will learn different methods to link spreadsheet data.
Setting up Multiple Sheet
Method-1 To add a new sheet, just click on the (+) sign at the left bottom of the spreadsheet.
Method-2 Follow the steps:
Step 1: Right-click on the sheet tab. A context menu appears.
Step 2: Click on the Insert Sheet option. The Insert Sheet dialog box appears. Step
3: Click on the required criteria. A new sheet will get inserted
You can also add a sheet from a different spreadsheet
file (for example, another Calc or Excel file).
Steps:
Step 1: Right-click on the sheet tab and click Insert
Sheet.
Step 2: Click on the From file radio button.
Step 3: Click on the Browse button.
Step 4: Select the file.
Step 5: Click on the Open button.
Step 6: Click OK. The sheet will be inserted.
Creating difference to other seats by using keyboard and mouse
To create the reference, follow these steps:
Step 1: Open both spreadsheets.
Step 2: Select the cell where formula will be entered.
Step 3: Press = from keyboard.
Step 4: Switch to the other spreadsheet.
Step 5: Select the sheet and then the referenced cell.
Step 6: Switch back to original spreadsheet.
Step 7: Click the green check mark on the input line.
Note: Any changes in the referenced sheet reflect in the final sheet.
Creating reference to other documents by using keyboard and mouse
Step 1: Open both spreadsheets.
Step 2: Click on the cell where result is required.
Step 3: Type = and a formula (e.g. SUM, MAX, MIN).
Step 4: Click the cell from the other sheet to reference.
Step 5: Insert a comma.
Step 6: Click next
required cell.
Step 7: Press Enter.
Hyperlinks to the Sheet
Hyperlinks allow you to jump to another sheet, another spreadsheet, or even a website.
A hyperlink is a pointer to a resource. It can be text, image, or icon.
Types of Hyperlinks
Same sheet / worksheet
Another sheet
Different file
Webpage or Internet link
Absolute Hyperlink
Stores complete file path. If file is moved, link breaks.
Example: C:\Users\ADMIN\Downloads\[Link]
Relative Hyperlink
Stores path relative to current file location.
Example: Downloads\[Link]
Creating Hyperlink
Method-
Step 1: Open the spreadsheet.
Step 2: Click Insert → Hyperlink.
Step 3: Select Document and choose file.
Step 4: Select the document.
Step 5: Click Target to choose sheet or location.
Step 6: Click Apply & Close.
Step 7: Type hyperlink text.
Step 8: Click Apply & Close.
Step 9: Hyperlink assigned.
Step 10: Press Ctrl + click to open hyperlink.
Editting Hyperlink
Step 1: Right-click hyperlink.
Step 2: Click Edit Hyperlink.
Delete the Hyperlink
Right-click and choose Remove Hyperlink.
Questions Answer
A. Multiple Choice Questions (MCQs)
Q.1 Insert Sheet dialog can be invoked from ______.
(a) Sheet✅ (b) Insert (c) Tools (d) Windows
Q.2 ______ refers to cell G5 of sheet named MySheet.
(a) $My Sheet'.G5 (b) $My Sheet'.G$5 (c) $ 'MySheet'.G5✅ (d) $ 'MySheet' .G5 Q.3
The path of a file has ______ forward slashes.
(a) Four (b) Two ✅ (c) Three (d) One
Q.4 Which feature is used to jump to another spreadsheet from current spreadsheet in
LibreOffice Calc?
(a) Macro (b) Connect✅ (c) Hyperlink (d) Copy
B. Fill in the Blanks
Q.1 A relative hyperlink stores the location with respect to the ______ location.
Current
Q.2 While inserting tables from a webpage ______ selects the entire HTML
document.
HTML_all
Q.3 The extension of LibreOffice base is ______. .odb
Q.4 ______ are used to enclose sheet names as there might be a space within sheet
names.
Single quotes (‘ ’)
Q.5 The From file option of ______ dialog box allows to insert sheet from another
file. Insert Sheet
C. True/False
Q.1 A sheet can only be added before the current sheet. False
Q.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. True
Q.3 It is not possible to link a sheet as a reference in another sheet. False
Q.4 You can insert data from a table created on a web page into a spreadsheet.
True
Q.5 A hyperlink once created on a sheet cannot be deleted. False
D. Answer the Following Questions
Q.1 Name the two ways to link sheets in LibreOffice Calc.
Answer: The two ways to link sheets in LibreOffice Calc are:
1. Create a Reference:
In this method, a cell or range from another sheet is referenced using formulas, such as
=Sheet2.A1. Any change in the original data automatically reflects in the linked cell.
2. Link External Data:
This method is used to link data from external files or webpages using the External Links
feature. It keeps the sheet updated with the latest data from the external source. Q.2
Differentiate between Relative and Absolute Hyperlink.
Answer:
Q.3 Write steps to extract a table from a web page into a spreadsheet.
Answer: To insert a table from a webpage into Calc, follow these steps:
Step 1: Open the spreadsheet where you want to insert web data.
Step 2: Select the cell where the first value of the web table should be placed.
Step 3: Click Sheet → External Links.
Step 4: In the dialog box, enter the URL of the webpage.
Step 5: Select the table name from available sources list.
Step 6: Click OK to import the table into the spreadsheet.
This process fetches and inserts online table data automatically into Calc.
Q.4 Write steps to register a data source that is in .odb format.
Answer: To register an .odb database file in LibreOffice:
1. Open LibreOffice.
2. Go to Tools → Options.
3. Under LibreOffice base section, click Databases.
4. Click New and browse to select your .odb file.
5. Give a name to data source.
6. Click OK to save.