+ Reply to Thread
Results 1 to 9 of 9

Should I use getpivotdata for my problem?

  1. #1
    Registered User
    Join Date
    01-27-2011
    Location
    NJ
    MS-Off Ver
    Excel 2007
    Posts
    6

    Should I use getpivotdata for my problem?

    Hi!

    I am having difficulties thinking through a solution to a problem I am having.

    I have a pivot table with ID#'s grouped within Model Names at the left and then at the top I have Months (Jan - Dec) In the data I have the serial#s of the models counted.

    E.g.
    Jan Feb ...
    ID1 - Model 1 1
    ID1 - Model 2 2
    ID1 TOTAL 1 2
    ...

    See the attached file for the specifics

    I have a display sheet with ID#'s in a drop down
    I then have modeled names down the left and months across the top.

    Not all IDs have the same models and not all months have models with counts either.

    I'm trying to use the pivot table as the data source to populate the display sheet depending upon what ID# was selected from the drop down.

    I was thinking of using getpivotdata, but I'm not too sure how to go about incorporating the ID#'s to drive the whole thing.

    Any assistance would be greatly appreciated!
    Attached Files Attached Files
    Last edited by fixedhalo; 01-30-2011 at 01:18 PM. Reason: Updated with file attachement [Mod: removed (Update) from title]

  2. #2
    Registered User
    Join Date
    12-27-2010
    Location
    Bolivia
    MS-Off Ver
    Excel 2007
    Posts
    16

    Re: Should I use getpivotdata for my problem?

    Hey fixedhalo
    It would be easier to help if you post a workbook, showing what you have and what you want.
    regards,
    selopezr

  3. #3
    Registered User
    Join Date
    01-27-2011
    Location
    NJ
    MS-Off Ver
    Excel 2007
    Posts
    6

    Re: (Update)Should I use getpivotdata for my problem?

    Any ideas with the posted file?

  4. #4
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: (Update)Should I use getpivotdata for my problem?

    Assuming no backwards compatibility requirement it would be better to use COUNTIFS on Display sheet.
    It's hard to provide a sample of the above given the values in B9:B19 are hard to reconcile with source data.

  5. #5
    Registered User
    Join Date
    01-27-2011
    Location
    NJ
    MS-Off Ver
    Excel 2007
    Posts
    6

    Re: Should I use getpivotdata for my problem?

    Ah! dangit I forgot to fix the display sheet to match the data. Okay, the display sheet now matches the data and the pivot sheets. My apologies. And to clarify, this should also be readable by folks using 2003.... *mutters* damm slow company*
    Last edited by fixedhalo; 01-30-2011 at 01:19 PM.

  6. #6
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: Should I use getpivotdata for my problem?

    It's not clear how relevant Year is but based on the sample and 2003 requirement:

    Please Login or Register  to view this content.
    the alternative to the above would be to create a concatenated key on MAT sheet and use COUNTIF with concatenated criteria / concatenated key
    Last edited by DonkeyOte; 01-30-2011 at 01:28 PM.

  7. #7
    Registered User
    Join Date
    01-27-2011
    Location
    NJ
    MS-Off Ver
    Excel 2007
    Posts
    6

    Re: Should I use getpivotdata for my problem?

    Awesome! It works! Thank you.

    So now that you provided the formula for it. Could you please explain to me your logic in using the lookup? I'm also not sure what 9.99e+307 means or how it relates within the sheets.

    Thank you again!

  8. #8
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: Should I use getpivotdata for my problem?

    9.99E+307 is a big number (9.99*10^307)

    The GETPIVOTDATA will fail if no value exists for the combination.
    Rather than double evaluate the GETPIVOTDATA (no IFERROR prior to XL2007) we can use this LOOKUP construct as an alternative.
    The CHOOSE generates a 2 value array - 0 (default) and the GETPIVOTDATA result.
    The LOOKUP (with big number criteria) will return the last number that appears within the 2 value array.
    It follows that if the GETPIVOTDATA succeeds the resulting value is returned, if not 0 is returned (default).

  9. #9
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,164

    Re: Should I use getpivotdata for my problem?

    Hi,
    Look on your MAT sheet for a possible Pivot Table answer. I didn't see 2003 vs 2007 requirements..
    Attached Files Attached Files
    One test is worth a thousand opinions.
    Click the * Add Reputation below to say thanks.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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