Pull data from pivot table based on date

Hi all,

I have a large pivot table with account info over several years, and would like to pull selected data from the pivot table to create some graphs.

I have the pivot table on Sheet 2, and a smaller table with only the selected data on Sheet 1. I would like to then create the graphs from the smaller table on Sheet 1.

My formula is =GETPIVOTDATA("Sales";Sheet2!$E$4;"Date";DATE(2011;1;1);"Account";1)

So basically what I want is the DATE() part of the above formula to be filled in based on the value of a cell in Sheet 1. Something like DATE(Sheet 1!B3). Any ideas?

Thanks!

Darrel

Thanks for all the feedback. Unfortunately I'm still stuck. The reason being that my pivot table contains all the account numbers as in the sample, and the graphs will have to show both costs and revenues, as well as some calculated percentages etc.. So it doesn't work to separate the pivot table into two and then make two separate graphs. Also, the figures of interest are the totals for the respective revenue and cost accounts. When I try and make a graph based on the pivot table, it holds the accounts separate meaning I get 6 different lines when I want 1.

I hope this helps explain why I've been trying to extract the data into a second table, and then make the graphs from the second table as opposed to directly from the pivot table. The end goal is that the user will define the time period (or rather, define the current month and the start point will be defined as current month minus 3 years), and the graphs will update accordingly.

I managed to find a different thread which works perfectly for me, http://www.excelforum.com/excel-form...vot-table.html

The formula is this
=GETPIVOTDATA("Value",Pivot!$B$2,"Currency",B$2,"Time Indicator",$A3)

Thanks anyway for your help