Two Questions involving GetPivotData

[Aqualion]

Hi,

There's two things that I've been trying to accomplish with pivot table data.

I am extracting information from a pivot table that has columns with these headings:

2015-02-01 | 2015-03-01 | 2015-04-01 | etc.

I will note here that the day of the month is not important to what I'm doing. But I am putting the dates here exactly as they appear in the data.

I have a GetPivotData function that looks like this: =GETPIVOTDATA("2015-05-01",Transactions!$A$1,"Category1",A7,"Category2","0"). This function works. However, I need to use this formula for multiple dates and multiple values for Category2, and then add them together. I'm having trouble figuring out how to achieve this with the formulas.


The first thing: The date in the data field. Rather than have the date just hard inserted into the formula, I would like to point the data field to cell that contains a date AND have it subtract a number of months from that date.

For example, let's say that Cell A1 contains 2015-07-01. I would like to enter something that works like GETPIVOTDATA("month(A1)-2",Transactions!$A$1,"Category1",A7,"Category2","0") so that the actually date being referenced is 2015-05-01.

Is this possible?


The second thing: In the example above, I have a 0 as the item for "Category2." I need to add the results of the same formula with that item for 0-6, the result being equivalent to:

GETPIVOTDATA("2015-05-01",Transactions!$A$1,"Category1",A7,"Category2","0")+
GETPIVOTDATA("2015-05-01",Transactions!$A$1,"Category1",A7,"Category2","1")+
GETPIVOTDATA("2015-05-01",Transactions!$A$1,"Category1",A7,"Category2","2")+
GETPIVOTDATA("2015-05-01",Transactions!$A$1,"Category1",A7,"Category2","3")+
GETPIVOTDATA("2015-05-01",Transactions!$A$1,"Category1",A7,"Category2","4")+
GETPIVOTDATA("2015-05-01",Transactions!$A$1,"Category1",A7,"Category2","5")+
GETPIVOTDATA("2015-05-01",Transactions!$A$1,"Category1",A7,"Category2","6")

Is there a way to program it to automatically run this formula from 0 to 6?


Thank you in advance for any assistance that you can provide!

[oeldere]

You get better help on your question if you add a small excel file, without confidential information.

Please also add manualy the expected result in your file.