+ Reply to Thread
Results 1 to 4 of 4

Pivot table reference formula

  1. #1
    Registered User
    Join Date
    08-19-2015
    Location
    Madison, Wi
    MS-Off Ver
    2013
    Posts
    17

    Pivot table reference formula

    hello,

    im trying to use the getpivotdata formula in excel 2013 to grab the YTD total for given amounts in each period of data. I'm not sure how to reference this in a formula since the Grand total column is not a specific column in the pivot table data. Here's a better picture of what I'm referring to below.

    Expense category Period Grand Total(YTD)
    1 2 3 4

    Architecture:

    Consulting exp $ ... $ ... $ ... $ ...
    Hardware Exp $ ... $ ... $ ... $ ...
    Depreciation $ ... $ ... $ ... $ ...

    IT Operations

    Consulting Exp $ ... $ ... $ ... $ ...
    Hardware Exp $ ... $ ... $ ... $ ...
    Depreciation $ ... $ ... $ ... $ ...


    Basically I'm trying to grab the grand total column for each individual Business Area/expense amount in that area. I have the following formula setup that is able to grab the specific expense items for each business are. I just don't know how to syntax the grand total(YTD) column.

    =IF(ISERROR(GETPIVOTDATA("Amount",Actuals!$A$4,"Period",'Period 4'!N$1,"IT Leaders",'Period 4'!B$50,"GL Map2",B51)),0,GETPIVOTDATA("Amount",Actuals!$A$4,"Period",'Period 4'!N$1,"IT Leaders",'Period 4'!B$50,"GL Map2",B51))

  2. #2
    Forum Expert
    Join Date
    10-09-2012
    Location
    Dallas, Texas
    MS-Off Ver
    MO 2010 & 2013
    Posts
    3,049

    Re: Pivot table reference formula

    Can you post a sample dataset in excel that has a few examples with hard-coded numbers indicating where you want a formula to automatically populate?
    Please ensure you mark your thread as Solved once it is. Click here to see how.
    If a post helps, please don't forget to add to our reputation by clicking the star icon in the bottom left-hand corner of a post.

  3. #3
    Registered User
    Join Date
    08-19-2015
    Location
    Madison, Wi
    MS-Off Ver
    2013
    Posts
    17

    Re: Pivot table reference formula

    111111111111111111111111
    Attached Files Attached Files
    Last edited by jPrisk; 05-09-2016 at 03:33 PM.

  4. #4
    Forum Expert
    Join Date
    10-09-2012
    Location
    Dallas, Texas
    MS-Off Ver
    MO 2010 & 2013
    Posts
    3,049

    Re: Pivot table reference formula

    Where is the pivot table?

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 5
    Last Post: 06-17-2014, 10:16 AM
  2. Reference the number of changing data in a formula from pivot table
    By jonathan.haynes in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 10-23-2013, 07:57 AM
  3. Replies: 2
    Last Post: 05-24-2013, 03:58 PM
  4. using an index formula to reference a pivot table
    By coffee_man in forum Excel General
    Replies: 0
    Last Post: 02-20-2012, 05:17 PM
  5. How to Reference a Pivot Table
    By uglion5 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-23-2011, 10:18 AM
  6. [SOLVED] VBA Code for a pivot table to open database and to reference table current page
    By Pete Straman Straman via OfficeKB.com in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 02-21-2005, 12:06 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1