Page 154 - Excel Data Analysis

08 537547 Ch07.qxd 3/4/03 12:07 PM Page 140

EXCEL DATA ANALYSIS

RETRIEVE A VALUE FROM

A PIVOTTABLE REPORT

ou can add PivotTable values to other worksheets need to specify that information as arguments for the
containing data analysis by creating a separate GETPIVOTDATA function.
Y formula to retrieve values from the PivotTable. If you The GETPIVOTDATA function has two required arguments
want to retrieve a value from a PivotTable into a worksheet
and up to 28 pairs of optional arguments. The Data_field
cell location, you can use the GETPIVOTDATA function. You argument expects the name of the Data area field for the
use this function, instead of a simple cell reference, to value you want to retrieve enclosed in quotes. For example,
dynamically display the values of a PivotTable based upon if you want to retrieve a Sales amount, and the Data field is
the filters on the PivotTable. For example, when you view Sales, you specify a value of "Sales" in this field.
the entire PivotTable report, cell E4 may contain the total
sales in California in January. But, if you filter the report to The next argument is Pivot_table, which requires a cell
only show sales from California and Texas, that value may reference to the PivotTable. Remember, if the PivotTable is
now display in cell B4. See the section "Filter a Field" for in a different worksheet, the reference must include the
more information on filtering. worksheet name.
To use the GETPIVOTDATA function, you must provide The remaining arguments identify the value you want to
descriptive information about the PivotTable value you retrieve. You must define the field and item. For example, to
want to retrieve, including the name of the Data area field, retrieve sales total from January, you specify a Field value of
and the corresponding Column area and Row area items. "Month" and an Item value of "January".
For example, if you wanted the hardware sales for Texas you
RETRIEVE A VALUE FROM A PIVOTTABLE REPORT

GETPIVOTDATA

⁄ Select the formula cell. ¤ Click Insert ➪ Function. ■ The Insert Function dialog Note: See Chapter 4 for more
box displays. information on inserting functions.
‹ Select the › Click OK.
GETPIVOTDATA function.

140