AboutBob Umlas Expertise I`m a Microsoft Excel MVP (Most Valuable Professional) and have been since the inception of the program in 1995. I can answer every kind of Excel question except: API, Importing/exporting to other programs (powerpoint, word,...)
Experience Worked with MS Excel since version 0.99 (on the Mac!). Was contributing editor to Excellence Magazine, having written >300 articles. John Walkenbach said of me "I finally met someone who knows as much about Excel as I do."
Publications Excellence, The Expert, Microsoft
Awards and Honors MVP
Led sessions for the Convergence 2004-2006 seminar on Excel tips & tricks
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
About Excel
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here
