GETPIVOTDATA - Help!

[sarahrosenberg]

I am trying to use GETPIVOTDATA on a pivot table with a lot of blank cells. The top of the pivot table is a "month/year" field. What I am trying to do is look up prices as of a certain date. In some instances the price data for that date is blank and I need my lookup formula to look up the previous price. For example, if the prices in my pivot are as follows:

01/06: $100
02/06: <blank>
03/06: <blank>
04/06: $85
05/06: $85
06/06: $78
07/06: <blank>
08/06: $75

I need my formula to pull $100 for 02/06 and 03/06 and $78 for 06/06. I pulled all the data - filling in the blanks - into a new table, but I have two row filters in addition to the data column filter and don't know that it is possible to do a vlookup / match with three filters?

Help!

Sarah

 

[mtb'r]

If your pivot table is in rows 1-3, the dates to lookup are in column A, and the lookups begin with January in cell B5, then cell B6 could be the following:

=IF(GETPIVOTDATA($A$1, A6)=0,B5,GETPIVOTDATA($A$1, A6))

So, if the current lookup is $0, then the formula returns the previous lookup value.

 

[sarahrosenberg]

That is how I populated my table, but now I have a huge data set and I need to pull the data in. I can't figure out how to pull it in from the pivot table or the table I built.

 

[mtb'r]

It sounds like your question is more of a problem of filtering more than 3 conditions, rather than GETPIVOTDATA?

What are your 3 filters?

Usually SUMPRODUCT is good for these types of situations. It's like using SUMIF for multiple criteria. This site has some useful tips on how to do that:
http://www.excel-vba.com/e-formula-sumproduct.htm