Difficulty

Apache OpenOffice 4.1 Calc Guide

Chapter 8: Using Pivot Tables

< Previous Section

Next Section >

• Introduction
• Examples with Step-by-Step Descriptions
  • Example 1: Sales Volume Overview
    • Practical Problems and Questions
    • Solution
    • Advantages
  • Example 2: Timekeeping
    • Practical Problems and Questions
    • Solution
    • Differences and Advantages
  • Example 3: Frequency Distribution
    • Solution with an Array Formula
    • Solution with a Pivot Table
• Functions in Detail
  • The Database
  • Start
  • Data Sources
    • Calc Spreadsheet
    • Registered Data Source
  • The Pivot Table Dialog
    • Basic Layout
    • More Options
    • More Settings for the Fields – Field Options
    • Data Fields
    • Row and Column Fields
    • Page Fields
  • Working with the Results of the Pivot Table
    • Start the Dialog
    • Change Layout by Using Drag and Drop
    • Grouping Rows or Columns
    • Grouping of Categories with Scalar Values
    • Grouping of Categories with Date or Time Values
    • Grouping without the Automatic Creation of Intervals
    • Sorting the Result
    • Drilling (Showing Details)
    • Filtering
    • Updating or Refreshing Changed Values
    • Cell Formatting
  • Multiple Data Fields
  • Shortcuts
• Function GETPIVOTDATA
  • Difficulty
  • Syntax
    • First Syntax Variation
    • Second Syntax Variation

< Previous Page

Next Page >

Difficulty

Normally, you create a reference to a value by entering the address of the cell that contains the value. For example, the formula =C6*2 creates a reference to cell C6 and returns the doubled value.

If this cell is located in the results area of a Pivot Table, it contains the result that was calculated by referencing specific categories of the row and column fields. In Figure 239, cell C6 contains the sum of the sales values of the employee Hans in the Sailing category. The formula in the cell C12 uses this value.

Figure 239: Formula reference to a cell of the Pivot Table

If the underlying data or the layout of the Pivot Table changes, you must be mindful that the sales value for Hans might appear in a different cell. Your formula still references the cell C6 and, therefore, uses the wrong value.

The correct value is in a different location. For example, in Figure 240, the location is now C7 because the employee Fritz is included in the Pivot Table.

Figure 240: The value that you really want to use can be found now in a different location.

The function GETPIVOTDATA allows you to have a reference to a value inside the Pivot Table by using the specific identifying categories of that value.