1 / 12

Referencing Pivot Table Cells with GetPivotData

Learn how to effectively use the GETPIVOTDATA function in Excel to reference values in pivot tables. With examples demonstrating how to retrieve the Grand Total from a specific game or avoid error messages when data is missing, this guide equips you with essential techniques. Discover how GETPIVOTDATA can automatically adapt to changes in pivot table size and position, providing you with accurate data without errors. Simplify your data analysis and enhance your Excel skills with practical tips and insights.

keith-alexander
Télécharger la présentation

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