MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Retriving daily sales wwith GETPIVOTDATA function

Posted by Chuck Dickens on February 06, 2002 11:38 AM

I have a daily sales report on a different sheet that I want to pull pivot table data into. There are different products on the pivot table and the sales numbers are calculated by date. I want to be able to pull the "widget" sales for "1/1/02" from the pivot table into my report. When I format the formula using "1/1/02" and the widget as variables the formula returns the error value #N/A.

I can do this by referencing the cell from the pivot table in my report, but I think this formula would work better (with less mistakes)because what you are asking is more specific.


Posted by Mark W. on February 06, 2002 12:23 PM

Group your date field by Days and Years, and then

=GETPIVOTDATA(ref,"Widget 1-Jan 2002")

Posted by Chuck Dickens on February 07, 2002 12:46 PM

What exactly is the {ref} is that the location of the pivot table. Using ref did not work so I replaced it with the location of the pivot table. Now I get the #N/A error?

Posted by Mark W. on February 07, 2002 2:20 PM


...I should have documented my posting better.

If you look at the Help topic for the
GETPIVOTDATA worksheet function you'll notice
that it's 1st argument is Pivot_table, and....

"Pivot_table is a reference to a cell in
the PivotTable that contains the data you
want to retrieve. Pivot_table can be a cell
or range of cells in the PivotTable, a name
for the range that contains the PivotTable,
or a label stored in a cell above the PivotTable."

I was using "ref" to mean a "reference to a cell..."