Calculations linking sheets
Another powerful feature of Calc is the ability to link data through several worksheets. The naming of worksheets can be helpful to identify where specific data may be found. A name such a Payroll or Boise Sales is much more meaningful than Sheet1. The function named SHEET() returns the sheet number in the collection of spreadsheets. With this function, if you are currently on the third sheet counting from the left along the worksheet tabs, the number 3 is returned. If you drag the worksheets around to different locations among the tabs, the number will return the number referring to the current position of this worksheet.
|| Users may experience a problem when creating a formula that references other worksheets. If you use the function SHEET() the number of the current worksheet appears even if you enter the name of the worksheet desired, e.g., =SHEET(Branch4) and you have as the top worksheet named CombinedOperations. This function only refers to the current or top worksheet and not to other worksheets found in the collection or file.
An example of calculations obtaining data from other work can be seen in a business setting where a business combines its branch operations into a single worksheet.
|| Sheet containing data for Branch 1.
|| Sheet containing data for Branch 2.
|| Sheet containing data for Branch 3.
|| Sheet containing combined data for all branches.
Combining data from several sheets into a single sheet.
To accomplish the links, it can be easier if the Function Wizard is used.
- Open the Function Wizard by clicking the f(x) button on the toolbar, or by selecting Insert > Function, or by pressing Ctrl+F2.
- Select a category of functions to shorten the list, then scroll down through the named functions and select the required one. When you select a function, its description appears on the right-hand side of the dialog. Double-click on the required function.
- The wizard now displays an area to the right where you can enter data manually in text boxes or click the Shrink button [[Image:]] to shrink the wizard so you can select the cells from the worksheet.
- To select cells, either click directly upon the needed cell or hold down the left mouse button and drag to select the needed area.
- When the area has been selected, click the Shrink button again to return to the wizard.
- If multiple ranges are needed, then select the next text box below the first and perform the same task for the next range. The wizard will accept in the Sum function up to 30 such ranges.
The following figures show how to create the combined sheet shown in the example above.
Selecting the SUM function in the Function Wizard.
Further steps in selecting cells containing data.
Completed entries in Function Wizard.