54 Reference Data Outside This Sheet

 <  Day Day Up  >  

If all your data resided in the current sheet, referencing other cells would be simple. You'd only need to know the other cell 's address, such as D4 . What if the cell is in another sheet inside the current spreadsheet? If Sheet1 needs to reference cell G6 in a sheet named Sheet3 , you cannot use the simple G6 reference.

Before You Begin

41 Enter Simple Data into a Spreadsheet

45 Edit Cell Data


See Also

63 Conditionally Format Data


Perhaps the data you need isn't even in another sheet but resides across your network somewhere. Or, perhaps, the data resides across the world, accessible from the Internet. That's no problem for Calc. You can insert network addresses and Web address links anywhere in a spreadsheet to display data from that location.

TIP

graphics/tipbw_icon.gif

If you use a spreadsheet with multiple names, consider renaming the default sheet names of Sheet1 , Sheet2 , and Sheet3 to names that are more meaningful, such as Division1Sales , Division2Sales , and Division3Sales . Right-click the sheet name 's tab and select Rename to rename, insert, or delete any sheet.


  1. Prepare for Entries

    Set up your sheet so it's ready for entries from other sheets and even from other locations. Of course, you can always add the labels after you reference data from other locations too.

  2. Enter a Sheet Reference

    To reference a cell from another sheet, preface your cell address with the sheet's name followed by a period, followed by that sheet's cell you want to reference. For example, to display in the current sheet the value from cell G19 of a sheet named Division2 , you'd type the following value in the current sheet's cell:

     

     =  Division2.G19  

    graphics/07inf09.jpg Calc locates the value in cell G19 of the sheet named Division2 and places it in the current cell.

    TIP

    graphics/tipbw_icon.gif

    You aren't limited to displaying values from other sheets. You may also want to use those values in calculations and functions. For example, the following duration function uses arguments from three other sheets:

     

     =Duration(Region1.G45, Accounting.PValue, Finance.FutValue*.9) 

    Two of the values are range names given to individual cells: PValue and FutValue .

  3. Enter Internet-Based Data

    If you want to reference a value from a spreadsheet stored on the Internet, feel free to do so. Obviously, an always-on Internet connection is best for such a reference. Otherwise, Calc will dial your modem connection to get the value every time you recalculate the spreadsheet.

    Use the following pattern:

     

     ='http://www.YourDomain.com/Spreadsheet.sxc'#Sheet1.Cell 

    That's quite a mouthful! Here is one such example:

     

     ='http://www.simplerenthouses.com/corporate.sxc'#Division1.W11 

    To read such a long reference, it helps to begin at the right. This references cell W11 in the sheet named Division1 in a spreadsheet named Corporate.sxc on a Web site named www.SimpleRentHouses.com ; keep in mind that you must enclose the Web page reference inside single quote marks.

    NOTE

    graphics/notebw_icon.gif

    Calc adds a dollar sign ( $ ) to your external sheet name to keep it an absolute reference. Also, Calc will rename your Web page reference to all lowercase letters if you type any in uppercase.

 <  Day Day Up  >  


Sams Teach Yourself OpenOffice.org All In One
Sams Teach Yourself OpenOffice.org All In One
ISBN: 0672326183
EAN: 2147483647
Year: 2003
Pages: 205
Authors: Greg Perry

Similar book on Amazon

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net