Excel 2000 GETPIVOTDATA With multiple fields

[Samait]

Hello. I think i have hit a brain block...i'm hoping you can help me. I am using Excel 2000 and need to get some things accomplished with GETPIVOTDATA. Here's the set up...

Data value: AvgUnitCost

Column Headers: Month... jan, feb, etc...

Row Fields:
GPPG (examples: Industrial, HWT, HWF)
CGPPG (Examples: Filmpaper, Other, Raw)


Ok... so if I am looking to pull information from january (Jan) for the GPPG "Industrial" which has (for example) a CGPPG of "Raw" @ .49 (data value) and another CGPPG of Other @ $17.49. Now, the GPPG "Industrial" would have a total line that showed 8.75...the average of the two values. If I want to write formula with GETPIVOTDATA to obtain, for example, the total line, i would think it should be
=GETPIVOTDATA(A1,"INDUSTRIAL JAN") but that doesn't work when i have the secondary row field of CGPPG in there. If i pull CGPPG out, then it works, but i need it there because another one i need to get is, for example, the value of just the GPPG INDUSTRIAL CGPPG Raw in january. So i would think it would be =GETPIVOTDATA(a1,"INDUSTRIAL RAW JAN") But that's not working either.

Please help me.

THANK YOU!!!

S.

 

[SkipVought]

Hi,

Why not use the DGET function on the source data?

Or you might be able to use the SUMPRODUCT function.

Skip,

_[red][/red]

 

[Samait]

I've never used DGET before....here's the thing though, Every month I will be pulling more information from the pivot table, based upon the same criteria with only the month changing. Now, the report i am working on will have the data for Jan, Feb, Mar, etc... as the year moves on, so i have to keep the month headers on the pivot table so that i can pull month appropriate information. I was hoping to have a simple getpivotdata formula so i could copy/paste and only have to change the month portion of the formula. Is there a way to do that with DGET or SUMPRODUCT?

 

[Samait]

i got it! I created new columns and used a formula like =a2&"1" to create new unique names for the second & third row fields. The problem was like this...

GPPG: Industrial
CGPPG: Industrial
CIPPG: raw

Getpivotdata couldn't figure out what industrial i wanted, so now it would be:

GPPG: Industrial
CGPPG: Industrial1

and it works! Thank you for your help!