GETPIVOTDATA - Help!

sarahrosenberg

Board Regular
Joined
Aug 27, 2002
Messages
190
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
 

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

mtb'r

New Member
Joined
Sep 8, 2006
Messages
42
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

Board Regular
Joined
Aug 27, 2002
Messages
190
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. :cry:
 

mtb'r

New Member
Joined
Sep 8, 2006
Messages
42
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
 

Forum statistics

Threads
1,141,759
Messages
5,708,361
Members
421,566
Latest member
7Nabisco

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top