Expert: Bob Umlas - 10/15/2008

Question
Hello Bob,

I was hoping you could help me with the getpivotdata function.  I've been trying to learn as much as possible about pivot tables in the past week and I've ran into a problem that I can't seem to find a solution in excel 2000.  I have a pivot table with location, amount, product type and date as its headings.  I am referencing the pivot table in another worksheet called “total” in the same workbook.  I've used the following GETPIVOTDATA formula to reference the data from pivot table in total worksheet:

=GETPIVOTDATA(BMPT!A3,"sum of amount 651 10 10/1/2008")

Where, bmpt is my pivot table,
651 is my location
10 is the product type
10/1/2008 is the date.

Now to the problem that I am having.  I want to be able to make the date part of the getpivotdata function as a cell reference so I could just drag the getpivotdata function down the cell on my worksheet so it can pick data for 10/2/2008,10/3/2008 and so on down the column.  I've tried many times but they all give me errors.  Any help is very much appreciated.  Sorry for a long question, trying to give you as much info as possible.

Thanks,

Ray


Answer
When you reference a date in a cell, like ="Today is "&A1 and A1 contains 10/1/2008, you'd see Today is 39722. this is a Serial# and you can get what you want this way (assuming the dats is in E3:
=GETPIVOTDATA(BMPT!A3,"sum of amount 651 10 "&TEXT(E3,"m/d/yyyy"))and you can fill this formula down.
HTH