+ Reply to Thread
Results 1 to 3 of 3

GETPIVOTDATA for a range

  1. #1
    Registered User
    Join Date
    11-29-2012
    Location
    Madison, WI
    MS-Off Ver
    MS Office 2010 (Excel, Word, Access)
    Posts
    72

    GETPIVOTDATA for a range

    I'm not sure if this is the right sub-forum for this, but hopefully I've got it right.

    I have a revenue report that I'm trying to redesign following a complete overhaul of our data system. I'm trying to streamline what used to be a thick stack of macros and dozens of data files into a pivot table that feeds into the report everybody's used to getting. For the monthly data, it's working pretty well.

    The problem I'm running into is my "Year to Date" values. What I'm trying to do is create a GetPivotData function that will pull all the values for a given accounting string, the current fiscal year, and for all monthly periods equal to or less than the current one.

    I know that I *could* do this by layering IfError statements and just creating the formula for each period, but that would rapidly get unwieldy to say the least (thirteen iterations of my monthly formula, which is already a combination of 4 different GetPivotData formulas due to fields where multiple accounting strings get added together... it would work out to something like a total of 52 different GetPivotData functions, and probably Excel reaching out of the computer to strangle me for doing that to it.)

    Any suggestions? I'd *really* like to just be able to do something like:
    Please Login or Register  to view this content.
    , which (to trim out all the excess references you don't need to get details on) would translate into:

    Please Login or Register  to view this content.
    However, Excel refuses to even try to process that.

    Any suggestions?

  2. #2
    Forum Expert
    Join Date
    12-15-2009
    Location
    Chicago, IL
    MS-Off Ver
    Microsoft Office 365
    Posts
    3,177

    Re: GETPIVOTDATA for a range

    I would highly recommend you turn off GETPIVOTDATA so that way you can reference your pivot table like normal range.
    Attached Images Attached Images

  3. #3
    Registered User
    Join Date
    11-29-2012
    Location
    Madison, WI
    MS-Off Ver
    MS Office 2010 (Excel, Word, Access)
    Posts
    72

    Re: GETPIVOTDATA for a range

    I tried that, but unfortunately now I can't get any of my SumIfs to work properly with it. I suspect I'll have to change my approach at this rate, but thanks for your advice. Any other suggestions, I'm willing to give them a shot.

+ 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. Help with GetPivotData
    By angeleenmc in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 05-28-2015, 09:33 AM
  2. [SOLVED] GETPIVOTDATA – How to force getpivotdata to accept missing data
    By scottc_00 in forum Excel Formulas & Functions
    Replies: 39
    Last Post: 02-11-2015, 03:23 PM
  3. Replies: 14
    Last Post: 06-11-2014, 03:32 PM
  4. [SOLVED] Getpivotdata for a date range criteria
    By kanonathena in forum Excel Charting & Pivots
    Replies: 4
    Last Post: 09-18-2013, 08:46 PM
  5. [SOLVED] Using GETPIVOTDATA
    By JakeMann in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 10-04-2012, 11:12 AM
  6. GETPIVOTDATA help
    By rbkgctc in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 02-07-2008, 11:54 AM
  7. Using an array range in GETPIVOTDATA
    By Steve in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 08-10-2006, 02:50 PM

Tags for this Thread

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