Ch 6 Linking Spreadsheet Data Notes Important Points
Ch 6 Linking Spreadsheet Data Notes Important Points
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.
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:
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
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: ‘file:///C:/Users/ADMIN/Documents/X-A.ods’#$Result.C4
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\try.ods 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\try.ods 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.
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.
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.
6. From the Available Tables/Ranges list, choose the desired table and click OK. (as shown below)
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.
Important links of Class X (IT – 402)
Unit 1: Digital Documentation (Advanced) using LibreOffice Writer
Chapter 1. Introduction to Styles – NOTES
Chapter 1. Introduction to Styles – Question Answers
Chapter 2. Working with Images – NOTES
Chapter 2. Working with Images – Question Answers
Chapter 3. Advanced features of Writer – NOTES
Chapter 3. Advanced features of Writer – Question Answers
Unit 2: Electronic Spreadsheet (Advanced) using LibreOffice Calc
Chapter 4. Analyse Data using Scenarios and Goal Seek – NOTES
Chapter 4. Analyse Data using Scenarios and Goal Seek – Question Answers
Chapter 5. Using Macros in Spreadsheet – NOTES
Chapter 5. Using Macros in Spreadsheet – Question Answers
UNIT 1: DIGITAL DOCUMENTATION (ADVANCED) – MCQ
UNIT-2: ELECTRONIC SPREADSHEET (ADVANCED) – MCQ
UNIT-3 RELATIONAL DATABASE MANAGEMENT SYSTEMS (BASIC) – MCQ
Disclaimer : I tried to give you the simple Notes of “Chapter 6 Linking Spreadsheet Data“ , but if you feel that there is/are mistakes in the Notes of “Chapter 6 Linking Spreadsheet Data“ given above, you can directly contact me at csiplearninghub@gmail.com. NCERT Book and Study material available on CBSE Website are used to create above article “Chapter 6 Linking Spreadsheet Data Notes Important Points“