In this chapter, you will understand Link spreadsheet data with Libre Office calc for class 10. So let’s begin!
Topics Covered
Link spreadsheet data with Libre Office Calc For Class 10
Link spreadsheet data is one of the important aspects of spreadsheet software. Sometimes the same data will entered in different worksheets and calculations may required. The master spreadsheet contains basic data and some of the fields from the master sheet need to be copied into another sheet.
In this scenario, linking is the key tool. Let us discuss Link spreadsheet data with Libre Office Calc For Class 10. Here we go!
Ways of linking spreadsheet data
There are three ways to link spreadsheet data.
You need to set up multiple sheets in the libre office for linking spreadsheet data. So let us begin with setting up multiple spreadsheets.
Setting up multiple sheets
Let’s take an example of a report card making system. To create a report card system, multiple sheets must be set up. They are:
- Student Profile – Contains basic data of students. This sheet has fields like rollno., name of the student, fathers’ name, mother’s name, date of birth, GRNo etc.
- Marks Entry – Contains data for marks entry. For each subject, the components are PT1, PT2, PT3, multiple tests, portfolio, subject enrichment activity, and theory marks, co-scholastic areas such as work education, art education, physical education discipline and attendance.
- Grade Computation – Contains data for each subject weightage for components like periodic tests, multiple tests, portfolio, subject enrichment, and term exam. Grades for all co-scholastic subjects and result status.
- Report Card – Contains report card format and linked cells for marks and grades.
Setting up multiple sheets by inserting new sheets
Let us begin setting up multiple sheets for creating report cards. In the beginning, create a master worksheet with basic data to be displayed in the report card. Here I have created a student profile sheet for the same. Follow these steps to create it:
- Open a new blank spreadsheet.
- Type the required data in the spreadsheet.
Rename a spreadsheet
[1] Click the sheet tab present at the bottom of the spreadsheet.
[2[ Choose Rename option from the context menu.
OR
Click on Sheet –> Rename Sheet option as displayed in the screenshot.
[3] A dialog box appears to enter new name of spreadsheet. Type the name of the spreadsheet as “Student Profile” and click on OK button.
[4] The sheet name will be renamed.
Insert a new spreadsheet
After completing the student profile spreadsheet, a new spreadsheet was created for the student marks entry. Hence, we need to insert a new spreadsheet for the student. Follow these steps to insert a new spreadsheet in the libre office calc.
[1] Click on Sheet –> Insert Sheet option as displayed in the screen shot.
OR
Right click on sheet tab and choose Insert Sheet option as below:
[2] The insert sheet dialog box appears as displayed below:
This dialog box has following options:
- Position: This option allows to choose the position where the new sheet is required to be inserted. The positions are:
- Before current sheet: By selecting this, the sheet will be inserted to the left side the current spreadsheet
- After current sheet: By selecting this, the sheet will be inserted to the right side of the current spreadsheet
- Sheet: This option has three options:
- New Sheet : Allows to insert a blank new sheet
- No. of sheets: If multiple sheets are required at time, this option allows to insert multiple sheet. Just enter the number of sheets are required and click on ok.
- Name: It allows to enter a name for sheet.
- From file: A sheet is required to be inserted from the file.
- OK and Cancel Buttons: Click on ok button for new speradsheet.
[3] A new spreadsheet opened in the workbook.
Creating reference to other sheets using keyboard
I have created a student profile sheet to maintain details to be displayed in report card. Now three more sheets will be prepared by linking the cell through creating references. These sheets are Marks Entry, Grade sheet and report card.
For marks entry, I am taking details like rollno and name from student profile spreadsheet.
Here the steps are given to create a reference using keyboard:
[1] Open the newly inserted spreadsheet in the workbook and place the cursor where reference is to be created. Here I am creating reference in cell A6.
[2] Now press = to create reference.
[3] Now press Ctrl + pg up key to move the cursor in a spreadsheet from where the reference is to be created. In my spreadsheet it is student profile roll no. column.
[4] Now press after selecting cell. The cursor automatically jumps to marks entry spreadsheet and display the roll no. i.e. 801.
[5] Now repeat the step 4 again for name column and create a reference for name. Then drag roll no and name for all roll number vertically.
[6] Now enter the marks for every subject.
While creating cell reference using keyboard the following keys should be remembered:
- Switch between spreadsheets – Ctrl + pg up (Left), Ctrl + pg dn (Right)
- Navigation keys – Relevant arrow keys among left, right, up and down
The cell reference in libre office calc starts with a $ sign. This $ refers a sheet name enclosed with a single quote followed by a . (dot) sign. Here in my example it is referenced as – $’Student Profile’.A2
Creating reference to other sheets using mouse
To create a reference using mouse follow these steps:
- Place the cursor where the cell reference is to be created.
- Now type = and move the sheet from which the cell reference is to be created.
- Select the cell which reference to be created.
- Release the mouse button when you finished.
To update the values automatically in referenced cells click on Tools –> Options –> LibreOffice Calc –> General –> Update (Always from trusted locations) option as displayed in the following screenshot.
Creating hyperlinks in spreadsheet
Hyperlinks are text written in the cell that allows to jump from one file to another. Hyperlinks can links more than one file as well as creates links of different parts of same file. A website or online links can be also created using hyperlinks.
Absolute and Relative Hyperlink
The hyperlink can be absolute or relative.
The absolute hyperlink contains the complete address (path) of the file. Hence when the file is shifted or removed from its original location, the link will be broken. The absolute hyperlink contains the file which is saved in different from where the spreadsheet is saved.
For example, C:\\LibreOfficeCalc\Files\Hyperlink.ods is an absolute hyperlink.
The relative hyperlink contains only the filename. The relative link file will be saved in the same folder where the spreadsheet is saved.
For example, The spreadsheet is saved in C:\\LibreOfficeCalc\Files\Hyperlink.ods and LibreOfficeCalc\Files\Photo1.jpg is relative link.
If the file is moved from the original location along with the directory then also the relative link will work as it is.
Creating hyperlink
To create a hyperlink, follow these steps:
[1] Select the cell in which the hyper link is to be created.
[2] Click on Insert –> Hyperlink option.
[3] The hyperlink dialog box will appear. The hyperlink dialog box looks like as follows:
The hyperlink dialog box has following options:
[4] Select appropriate option to link out of the above and apply desired settings.
[5] Click on OK button.
Editing Hyperlink
The steps to edit existing link are as follows:
[1] Place the cursor anywhere in the link and right click on it and choose the Edit hyperlink option from the context menu.
[2] Hyperlink dialog box will appear.
[3] Apply the desired settings and click on OK.
Delete hyperlink
To delete hyperlink, follow the steps given below:
[1] Place the cursor anywhere in the hyperlink.
[2] Right click on the hyperlink and choose the remove hyperlink option from the context menu.
[3] The hyper link will be removed.
Linking to external data
Internet is the big source of information. This information is stored in the websites which is collection of different web pages. These web pages are crated through HTML. The libre office calc allows to link these HTML pages in the form of external data.
The external data allows to copy the tables present in the website into the spreadsheet.
To open external data dialog box, follow the below given steps:
[1] Open the spreadsheet where external data is to be inserted.
[2] Select the cell to store the first cell of the table in the external data.
[3] Select Sheet –> External Links
[4] The External Data dialog box will appear as below:
The dialog box has following options:
- URL or External Data Source: Type the URL of HTML source file.
- Available Tables/Ranges: It shows the list of available tables or ranges from source.
- Update Every: Specify the time to update the source after a specific interval.
[5] Type the url in URL or external data source box and press enter. It will ask for import options. Click ok button.
Click here for the link used in this activity.
[6] Select any table in the available tables/ranges. I have selected HTML_11 table.
[7] The data will be inserted.
Linking to registered data sources
Libre office calc allows to linking with databases to other data sources. Register the database with libre office base. To register the database, follow the given steps:
[1] Click on Tools > Options > LibreOffice Base > Databases option. It will open this dialog box.
[2] Select the database or create new database by clicking on New button.
[3] I have created a new database. It will open dialog box to create a database link and click on registered name.
[4] When the ok button is pressed the database will be added to registered database.