1 / 12

Referencing Pivot Table Cells with GetPivotData

Referencing Pivot Table Cells with GetPivotData. Robert Rosen. GetPivotData example. A reference to the Grand Total value in game 2 in this pivot table would be:. =GETPIVOTDATA(“Yds",$A$1,“Game", 2 ). Upper left cell of pivot table. Data field name. Column field name. Column value.

keith-alexander
Download Presentation

Referencing Pivot Table Cells with GetPivotData

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Referencing Pivot Table Cells with GetPivotData Robert Rosen

  2. GetPivotData example A reference to the Grand Total value in game 2 in this pivot table would be: =GETPIVOTDATA(“Yds",$A$1,“Game",2) Upper left cell of pivot table Data field name Column field name Column value

  3. By default, clicking on a pivot table cell while entering a formula will insert a GetPivotData reference into the formula instead of a regular cell reference. • You can change this behavior in Excel options:

  4. Why use GetPivotData? Reason 1…

  5. Suppose a data refresh adds 4 new rows to our pivot table A regular cell reference to a cell in the grand total row (formerly row 13, now row 17) won’t show the Grand Total any longer… but a GetPivotData reference will!

  6. Note: In Our Previous Example… =GETPIVOTDATA("Yds",$R$197,"week",2) If you drag this reference one cell to the right to reference the value for fiscal month 3, the formula is not changed automatically. You have to manually change the 2 to 3.

  7. Why use GetPivotData? Reason 2…

  8. Suppose we want to add the values from these 2 tables for each week in 2 different years No data in the data source for this table for week 3 – Excel omits the column Sum of column values across tables is: Week 1 + Week 1 OK Week 2 + Week 2 OK Week 3 + Week 4 ..OOPS!

  9. If week 3 is in column F and table #2 starts at row 12, we could say instead: • =GETPIVOTDATA("Yds",$A$1,"week",3) + GETPIVOTDATA("Yds",$A$15,"week",3) • Will produce a #REF! error (since the second GETPIVOTDATA reference is to something that doesn’t exist – a week 3 column in the second table)

  10. The improved version: • =IFERROR(GETPIVOTDATA("Yds",$A$1,"week",3) + GETPIVOTDATA("Yds",$A$15,"week",3), GETPIVOTDATA("Yds",$A$1,"week",3) ) • i.e. if week 3 value in table 2 is missing, result is just the value in table 1 • The IFERROR function takes two arguments: • The value to show if evaluating it doesn’t produce an error • The value to show otherwise

  11. In Summary, GetPivotData References Can • Adapt to changes in pivot table size/position • Avoid potential erroneous results when referencing pivot tables with missing columns.

More Related