Follow Techotopia on Twitter

On-line Guides
All Guides
eBook Store
iOS / Android
Linux for Beginners
Office Productivity
Linux Installation
Linux Security
Linux Utilities
Linux Virtualization
Linux Kernel
System/Network Admin
Programming
Scripting Languages
Development Tools
Web Development
GUI Toolkits/Desktop
Databases
Mail Systems
openSolaris
Eclipse Documentation
Techotopia.com
Virtuatopia.com

How To Guides
Virtualization
General System Admin
Linux Security
Linux Filesystems
Web Servers
Graphics & Desktop
PC Hardware
Windows
Problem Solutions
Privacy Policy

  




 

 

Microsoft Office to OpenOffice Migration Guide
Previous Page Home Next Page

Working across spreadsheets, web pages and database data

Working across spreadsheets

Calc’s Navigator can be used to copy or link to data from another spreadsheet (source).

To insert a link (or reference) to or copy a cell or range of cells with the Navigator:

  1. Open the source and the target documents.

  2. Open the Navigator from the target document. Use the Edit > Navigator command or the F5 shortcut. Select the Drag mode you want to use (see ).

  • Insert as Hyperlink - creates a hyperlink back to the source file.

  • Insert as Link – insert a linked copy of the data in the target document.

  • Insert as Copy – copies the range to the target document, but does not link it to the source range.

  • Select the source document from the box at the bottom of the dialog.

  • Browse to the Range names category and open it by clicking on the plus (+) sign. If there are no named ranges in the source document there will be no ranges available for insertion.

  • Select and drag the range you want to link to the target sheet.

    Note

    If the cell range in the source spreadsheet you want to insert in has not been defined as a named range, define it by using the Define Names dialog accessible from the Insert > Names > Define menu item, or use the Ctrl-F3 shortcut. Only named ranges will appear in the Navigator.

    Frame5

    To refer to the contents of cells in another spreadsheet document, or workbook, the syntax is 'url of other workbook'#$sheetname.cellrange. For example, on a Linux system:

    'file:///home/hillview/Documents/Tests/Address.xls'#$Sheet1.A1

    or, on a Windows system:

    'file:///C:/Documents/Tests/Address.xls'#$Sheet1.A1

    To input these links, you can:

    1. Type in the formula up to the point where the link is required.

    2. Select Window > [to select the desired document].

    3. It is not obvious that this is working, but it is. Select the desired cell or range of cells.

    4. Select Window > [select the original document].

    5. Finish entering the formula.

    What is disconcerting is that the value displayed is based on the value of the cell (or range of cells) when the linked document was last saved.

    So, to check it, save the linked document and update the link in the document (Edit > Links) that contains the link.

    Note

    Links created using the prevous examples, including the Navigator’s drrg and drop method, do not update automatically.

    To update them, use Edit > Links > [select the desired link] > Update. The update works from the saved file – so if both files are open and the file that the link is based on is updated, that file must be saved before updating the link.

    To have persistent updating of data between two spreadsheets, insert Dynamic Data Exchange (DDE) links:

    1. Select and copy the range of cells to be linked to.

    2. Change to the spreadsheet that requires the link and select the

    3. Open the Paste Special dialog, Edit > Paste Special and check the Link option (the checkbox at the bottom left of the dialog).

    4. Click OK to insert the DDE link.

    These cells will have a persistent link to the contents of the other sheet and the values returned can then be used in other formulas.

    Links to HTML data or other spreadsheets

    To link to data on the Internet:

    1. In a browser navigate to the desired page.

    2. Copy the URL.

    3. In Calc, Insert > Link to External Data.

    4. Paste in the URL.

    5. This step is not obvious: press Enter, wait a moment and the bottom list box (called Available tables/ranges) will show:

    • HTML_all (the entire page)

    • HTML_tables (all the tables on the page)

    • HTML_1 (each table consecutively numbered on the page)

  • Select the desired option from the list box.

  • Optionally check the Update every checkbox and specify how often to update.

    The same process can be used for linking to data in spreadsheets.

    Note

    The source file must be saved before the update can take place. (In OOo, AutoSaves overwrite the original file, so with AutoSaves turned on, this feature could be more useful than it first appears.)

    Links to database data

    1. Display the Data source viewer (F4).

    2. In the Data Explorer window, navigate to the desired table or query (if it is not visible click Explorer on or off–left button in the Data source toolbar).

    3. Drag the table or query onto the sheet. (See Chapter 11, “Getting Started with Base” in the Getting Started guide for information about working with data sources, such as registering the data source.)

    Microsoft Office to OpenOffice Migration Guide
    Previous Page Home Next Page


  •  
     
      Published under the terms of the GNU General Public License Design by Interspire