Results 1 to 14 of 14
  1. #1
    New Lounger
    Join Date
    Feb 2007
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts

    getpivotdata EXCEL 2000 (Excel 2000)

    Hello all. I need some help. I'm trying to use GETPIVOTDATA in Excel 2000. Now, there are multiple fields used as rows, so it gets a bit confusing i think to explain it, therefore...i'm attaching a spreadsheet that shows u the data i have and the 3 things I am trying to figure out. If anyone can help me, I'd really appreciate it. I'm on a tight timeline...The final product was actually due today, but i seem to be on a standstill....I appreciate any and all help you can give me.
    Attached Files Attached Files

  2. #2
    3 Star Lounger
    Join Date
    May 2002
    Location
    Mpls, Minnesota, USA
    Posts
    271
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: getpivotdata EXCEL 2000 (Excel 2000)

    samareanna,

    I checked Help on the GETPIVOTDATA and found this:
    Returns data stored in a PivotTable report. You can use GETPIVOTDATA to retrieve summary data from a PivotTable report, provided the summary data is visible in the report.

    Note You can quickly enter a simple GETPIVOTDATA formula by typing = in the cell you want to return the value to and then clicking the cell in the PivotTable report that contains the data you want to return.

    I tried it in your workbook and it seemed to work fine. You can also then look at the structure of the formula to see where yours is different.

    Chuck
    Chuck Reimer
    I'm from the Government and I'm here to help...

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: getpivotdata EXCEL 2000 (Excel 2000)

    Welcome to Woody's Lounge!

    Simply type = then point to the cell with the number you want. Excel will create the formula for you.

    =GETPIVOTDATA("Comp Cost",$A$4,"GPPG","CTS","FM",1)

    =GETPIVOTDATA("Comp Cost",$A$4,"GPPG","HWF","CIPPG","RAW","CGPPG","COR ","FM",1)+GETPIVOTDATA("Comp Cost",$A$4,"GPPG","HWF","CIPPG","RAW","CGPPG","FCS/Back","FM",1)

    =GETPIVOTDATA("Comp Cost",$A$4,"GPPG","INDUSTRIAL","CIPPG","FILM","CGP PG","FILMPAPER","FM",1)+GETPIVOTDATA("Comp Cost",$A$4,"GPPG","INDUSTRIAL","CIPPG","FINISHED", "CGPPG","INDUSTRIAL","FM",1)

    For the 2nd and 3rd one: it's strange to add two averages!

  4. #4
    New Lounger
    Join Date
    Feb 2007
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: getpivotdata EXCEL 2000 (Excel 2000)

    The problem with me using the method of = and clicking is that since this is Excel 2000, it doesn't create the GETPIVOTDATA formula, it just references the cell, which may change upon refreshing. If I don't reference the field names, i lose the consistency. :-( I've already considered that method, but unless my company upgrades to 2002 or higher, i'm stuck with manually creating the formula. Problem is, i cannot seem to get this formula to work! LOL

  5. #5
    New Lounger
    Join Date
    Feb 2007
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: getpivotdata EXCEL 2000 (Excel 2000)

    Hans, i'm sorry...that won't work. In Excel 2000 you have to type the formula and the syntax is different. I'm adding the two averages because the two averages represent two different items required to make the end product. Obtained the average cost of each, then added them since they are both needed. :-)

    Ugh...there must be a way to do this. It's killing me. LOL

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: getpivotdata EXCEL 2000 (Excel 2000)

    OK, I've looked up the syntax of GETPIVOTTABLE in Excel 2000. It is *much* more limited than in Excel 2002 and later. If I read it correctly, it is not possible to do what you want in Excel 2000 - it only supports a simple intersection of two variables, not specifying multiple criteria.
    Let's hope that someone who still has Excel 2000 will prove me wrong...

  7. #7
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: getpivotdata EXCEL 2000 (Excel 2000)

    I checked out your scheme in XL97 (which I think uses the same GetPivotData as XL2000). You are doing it correctly and it would work if your data was not confusing excel.

    When you ask for "CTS Jan" it is not unique and excel is confused and gives you an #N/A error. You want CTS out of GPPG, but you also have a CTS in CGPPG.

    If you use something in GPPG that is unique like "vinyl":

    =GETPIVOTDATA(A4,"vinyl jan")

    You will get 4.12 as you would desire. You need to make sure the item names are unique so excel will not be confused

    Steve

  8. #8
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: getpivotdata EXCEL 2000 (Excel 2000)

    It can give the intersection of multiple columns if all are unique:

    =GETPIVOTDATA(A4,"REJECTRAW FILMPAPER FILM Jan")

    will work since each heading is unique.

    =GETPIVOTDATA(A4,"HWF COR RAW JAN")

    will give #N/A since neither HWF nor RAW are a unique entry in the table. HWG is an entry in GPPG and CGPPG. RAW is in CGPPG and CIPPG. I presume XL is confused as to which is desired.

    Steve

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: getpivotdata EXCEL 2000 (Excel 2000)

    Thanks for that, I don't have an older version of Excel any more to test on (well, actually I do, but it takes too much time to set up my old PC)

  10. #10
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: getpivotdata EXCEL 2000 (Excel 2000)

    Is it an option to use something like a SUMPRODUCT formula off the raw data rather than trying to get the information out of the pivot table directly?
    Regards,
    Rory

    Microsoft MVP - Excel

  11. #11
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: getpivotdata EXCEL 2000 (Excel 2000)

    I have XL2002 on my work laptop and XL97 at home. I tend to use the laptop since it is the only one at work, and with teenage son wanting to be on the computer at home, it is easier to just use the laptop at home also.

    Steve

  12. #12
    New Lounger
    Join Date
    Feb 2007
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: getpivotdata EXCEL 2000 (Excel 2000)

    I'm not sure... I've really not often used SUMPRODUCT so i'm not real familiar with it...I'll go look at the syntax and see if that will work. If it won't, then I think running a macro to add a digit to the end of the 2 other row headers will then provide me with the unique names i will need for the getpivotdata. :-) Either way, one of them should work...I'll check later this morning and let you know.
    BTW, thank you all so much for helping me with this. It is greatly appreciated.

  13. #13
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: getpivotdata EXCEL 2000 (Excel 2000)

    I suspect the easiest option is to create separate pivot tables with only the level of data you require for each of the <code>GETPIVOTDATA</code> formulas. That way you can keep the formulas simple! <img src=/S/grin.gif border=0 alt=grin width=15 height=15>
    Regards,
    Rory

    Microsoft MVP - Excel

  14. #14
    New Lounger
    Join Date
    Feb 2007
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: getpivotdata EXCEL 2000 (Excel 2000)

    The Getpivotdata worked awesome. I created a formula in a new column that added a 1 to the end of the names in the secondary row heading and a 2 to the end of the names in the third row heading. Worked like a charm creating new unique names. Thank you all so much. Absolute saints.

Posting Permissions

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