Combining Sheets and Linking to Other Spreadsheets

Spreadsheets are versatile creatures . Oftentimes your spreadsheet use is limited only by your imagination . Use the following procedures as a mental primer to help expand your understanding of what's possible with Calc.

Combining Data From Many Sheets Into One Sheet

It's good practice to use multiple sheets to organize spreadsheets (see Break It Up on page 586).

The main idea behind this procedure is to build separate pieces of information on different sheets, then include bits of data from each of the separate sheets onto another sheet (which we'll call a master sheet in this procedure). A good example of this is a profit and loss (P&L) report, which combines data from separate income and expense sheets to create a report on a third sheet, as shown in Figure 20-25. The data from the income and expense sheets is pasted into the P&L sheet as links, so that when the data on those sheets changes, the data on the P&L sheet updates automatically.

1. In one of the sheets, select the cell (s) you want to reference.

   You can select an individual cell or a range of cells that are adjacent. You can't copy a group of non-adjacent cells .

2. Choose Edit > Copy.

3. Switch to the master sheet, and click the cell(s) or inside of the formula you want to insert the reference into.

4. Choose Edit > Paste Special.

5. In the Paste Special window, select the Link option at the bottom of the window, and click OK.

   The data is inserted as a reference to the sheet it was copied from.

6. Repeat these steps for all data in other sheets you want to reference.

Figure 20-25. Combining cells in many sheets on a master sheet

As an alternative to using the Paste Special command, you can enter the reference manually using the following syntax:

=$SheetName.$A$1

(SheetName is the exact name that appears on the sheet's tab at the bottom of the Calc window.) Use that exact syntax (without the equals sign) when using that cell reference inside formulas.

Strictly speaking, this isn't a link by normal standards that is updated like a link (by choosing Edit > Links and clicking Update). It's a reference that is automatically or manually calculated (see Updating Calculations on page 571).

Note

Notice the use of the $ in the cell references. This signifies an absolute reference. For more information, see Relative and Absolute Cell References on page 568.

You can also insert entire sheets from other Calc files into a spreadsheet, by copy or by link. See Adding Sheets to a Spreadsheet on page 521.

About the Calc Consolidation Feature

Calc also has a feature called Consolidation (Data > Consolidate) that lets you combine and perform calculations on separate groups of data. The effect of creating links to data is identical to the more manual copy and paste special process in the previous procedure. However, Consolidate isn't an extremely intuitive tool, and you're likely to get more predictable results by using the previous copy/paste special procedure for combining separate pieces of data in one sheet or area and setting up calculations manually.

Linking to Data in Other Calc Files

This procedure is the same in concept to the previous procedure, Combining Data From Many Sheets Into One Sheet, except that you link to cells in other Calc files.

1. Open the file(s) containing the cells you want to reference in the current spreadsheet.

2. Select the cell(s) in the other spreadsheet file you want to reference.

   You can select an individual cell or a range of cells that are adjacent. You can't copy a group of non-adjacent cells.

3. Choose Edit > Copy.

4. Switch to the working spreadsheet file, and click the cell(s) or inside of the formula you want to insert the reference into.

5. Choose Edit > Paste Special.

6. In the Paste Special window, select the Link option at the bottom of the window, and click OK.

   Calc inserts the reference as a DDE link to the source file.

7. Repeat these steps for all the data in other spreadsheets you want to reference.

As an alternative to having other spreadsheet files open, you can enter the reference manually using the following syntax:

On Linux and Solaris

=DDE("soffice";"/home/docs/Filename.sdc";"'SheetName'.A1")

On Windows

=DDE("soffice";"C:\My Documents\Filename.sdc";"'SheetName'.A1")

The Windows example shows a path to a hard drive rather than to a network location. /home/docs and C:\My Documents are the paths to the file. The filename is the exact filename. SheetName is the exact name on the sheet tab containing the data. A1 is the cell being referenced. The SheetName is enclosed in single quotes, and the SheetName and cell reference are enclosed together in double quotes.

Use that exact syntax (without the equals sign) when using that cell reference nested within formulas.