CBSE Class 10CBSE Class 11CBSE Class 12CBSE CLASS 9

Ch 6 Linking Spreadsheet Data Notes Important Points – CS-IP-Learning-Hub

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.

Linking Spreadsheet data
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 dataLinking Spreadsheet data
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 dataLinking Spreadsheet data
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: ‘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.

Hyperlink - Linking Spreadsheet DataHyperlink - Linking Spreadsheet Data
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 DataLinking Spreadsheet Data
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 DataLinking Spreadsheet Data
Linking Spreadsheet Data

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 DataLinking Spreadsheet Data
Linking Spreadsheet Data

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


Ch 6 Linking Spreadsheet Data Notes Important Points

Ch 6 Linking Spreadsheet Data Notes Important Points

Ch 6 Linking Spreadsheet Data Notes Important Points

Ch 6 Linking Spreadsheet Data Notes Important Points

Show More
यौगिक किसे कहते हैं? परिभाषा, प्रकार और विशेषताएं | Yogik Kise Kahate Hain Circuit Breaker Kya Hai Ohm ka Niyam Power Factor Kya hai Basic Electrical in Hindi Interview Questions In Hindi