Chapter 6 Linking Spreadsheet Data PDF

Chapter 6 Linking Spreadsheet Data Setting up Multiple Sheets There are three ways to insert new sheet. 1.To add a new sheet in the spreadsheet, click on the Add Sheet by clicking on the (+) sign located in the left bottom of the spreadsheet. 2.Right click anywhere...

Chapter 6 Linking Spreadsheet Data Setting up Multiple Sheets There are three ways to insert new sheet. 1.To add a new sheet in the spreadsheet, click on the Add Sheet by clicking on the (+) sign located in the left bottom of the spreadsheet. 2.Right click anywhere on the sheet tab and select Insert sheet option from the drop-down list. It gives us a choice to put the new sheet, assign the name of the sheets, delete a sheet and so on. 3. Click on Sheet > Insert Sheet to open Insert sheet dialog box. Creating Reference to Other Sheets by Using Keyboard and Mouse Marks obtained in Term 1 and Term2 are stored in different sheets named Terml and Term2 respectively. To calculate the final marks for English in the cell C4 of Result sheet, follow the steps. Step 1. Click on the = icon next to the input line (or type = in cell C4), type Sum() and click between the brackets. Step 2. Now click on the Terml sheet and click the English Marks for the first student and add (,) comma for the next value Step 3. Next click on the Term 2 sheet and click the English Marks for the first student. Step 4. Use fill handle to fill the cells up to the last student's data. The result sheet gives the Total scored by each student in English. Note: Any changes made to marks in Term1 and Term2 sheet will be reflected in the result sheet as well. That is how the sheets are linked together. We had selected cell reference by clicking on the cells. An alternative could be to type the reference. 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 C4 of sheet named Term1 we will type:$'Term1’.C4 Note: Single quotes (‘ ’) are used as there is a space between Term and 1 in the sheet name. Creating Reference to Other Documents by Using Keyboard and Mouse 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 Note : 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. Insert a sheet from an existing file into the current document. Step 1. To insert the sheet “Result” from Result-X-A spreadsheet in the Result Analysis spreadsheet, open the Result Analysis spreadsheet. Step 2. Select Sheet > Insert Sheet > From File. Step 3. Click on Browse button, which will open a file selection dialog box. Step 4. Select the file from which you want to import the sheets. After selecting the file, the sheets contained in it are displayed in the list box. The file path is displayed below this box. Select the sheet to be inserted from the list box. Step 5. Click on the required “Result” sheet from the file “ResultX-A.ods”. Step 6. Check the Link checkbox to ensure that the changes made in the selected sheet “Result” are reflected. Step 7. Press OK. You will find the Result sheet added to the sheet tab. You could have also copied and pasted data but linking a sheet allows us to always have access to “live” data from another spreadsheet. A “live” data means that the data is always the same as in the original file. The links can be updated by selecting Tools > Options > LibreOffice Calc > General > Updating, whenever the file is opened to ensure that you have access to the updated data always. Hyperlinks to the Sheet-Hyperlink makes it possible to jump from a sheet in the same spreadsheet, different spreadsheet or a website by creating a hyperlink 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\DomnIoads\ try.odd 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. 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 To hyperlink a Result sheet of "Result-X-A" spreadsheet document in the "Result-X-B" spreadsheet document, follow the following steps: Step 1. Open spreadsheet document. Step 2. From the main menu, select and click on lnsert >Hyperlink. An Hyperlink dialog box will open Step 3. To insert the spreadsheet document, click on the Document on the left pan of dialog box, then to select the spreadsheet document, click on the button located after the Path. Select the required document. In our case we select the spreadsheet document “Result-X-A”. Step 4. Then click on the Target button to choose the sheet which is to be hyperlinked. Target in Document helps to specifically choose a target in the document such as sheet, tables, frames, images, headings and so on. Here in our case we will select the sheet Result. Step 5. Click on Apply and Close button to exit the Target Document window. Step 6. Enter the text in the Text box to assign the hyperlink to that text. In our case we have entered the text as “Result-X-A”. So the hyperlink will be assigned to the text “Result-X-A”. Step 7. Click on Apply and Close button to confirm the changes and exit the Hyperlink dialog box. Step 8. Observe that the hyperlink is assigned to the word “Result-X-A”. Step 9. 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 a Hyperlink To edit an existing link, place the cursor anywhere in the link and right click the hyperlink. A context menu will be displayed. Click on Edit Hyperlink..., the Hyperlink dialog box will be displayed, where you can make changes to the hyperlink. On clicking the Remove Hyperlink option, the link will be removed from the text and thus it will not point to any other location. Linking to External Data 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. The versatility of a spreadsheet allows us to insert tables from HTML documents into Calc. To insert the tables from a HTML document, we can use the External Data Dialog box. The steps for the same are given below. 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 in the webpage. From the Available Tables/Ranges list, choose the desired table. If you choose HTML_all option, then the entire HTML document is selected. Linking to Registered Data Sources Calc allows us to link spreadsheet documents with databases and other data sources. The data source needs to be registered with LibreOffice. Registration is a means to inform LibreOffice about the type of data source and the location of the file. The extension of LibreOffice Base is.odb. You can access a variety of databases and other data sources and link them to Calc documents. 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. Check Your Progress A. Multiple choice questions 1. Insert Sheet dialog can be invoked from _______________. (a) sheet (b) insert (c) tools (d) Windows 2. ______________ refers to cell G5 of sheet named My Sheet. (a) $My Sheet.’G5’ (b) $My Sheet_’G5’ (c) $ ‘MySheet’.G5 (d) $ ‘MySheet’_G5 3. The path of a file has ____________ forward slashes. (a) four (b) three (c) two (d) one 4. Which of the following feature is used to jump to a different spreadsheet from the current spreadsheet in LibreOffice Calc? (a) Macro (b) Hyperlink (c) connect (d) Copy B. Fill in the blanks 1. A relative hyperlink stores the location with respect to the ___________ location. 2. While inserting tables from a webpage _____________ selects the entire HTML document. 3. The extension of LibreOffice base is ______________. 4. __________ are used to enclose sheet names as there might be a space within sheet names. 5. The From file option of ___________ Dialog box allows to insert sheet from another file. C. State whether the following statements are True or False notes 1. A sheet can only be added before the current sheet. 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. 3. It is not possible to link a sheet as a reference in another sheet. 4. We can insert data from a table created on a web page into a spreadsheet. 5. A hyperlink once created on a sheet cannot be deleted. D. Answer the following questions 1. Name the two ways to link the sheets in a LibreOffice Calc. 2. Differentiate between Relative and Absolute Hyperlink. 3. Write steps to extract a table from a web page in a spreadsheet. 4. Write steps to register a data source that is in *.odb format. 5. State advantages of extracting data from a web page into spreadsheet