Results 1 to 14 of 14

20070216, 19:42 #1
 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.

20070216, 19:53 #2
 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.
ChuckChuck Reimer
I'm from the Government and I'm here to help...

20070216, 19:58 #3
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 28 Times in 28 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!

20070216, 19:58 #4
 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

20070216, 20:26 #5
 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

20070216, 20:41 #6
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 28 Times in 28 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...

20070216, 22:48 #7
 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

20070216, 22:56 #8
 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

20070216, 23:43 #9
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 28 Times in 28 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)

20070216, 23:44 #10
 Join Date
 Dec 2000
 Location
 Burwash, East Sussex, United Kingdom
 Posts
 6,270
 Thanks
 3
 Thanked 187 Times in 173 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

20070217, 09:51 #11
 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

20070219, 12:04 #12
 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.

20070219, 13:43 #13
 Join Date
 Dec 2000
 Location
 Burwash, East Sussex, United Kingdom
 Posts
 6,270
 Thanks
 3
 Thanked 187 Times in 173 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

20070219, 14:16 #14
 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.