can excel 2007 embed getpivotdata into a subtotal function?

[jeffreyweir]

Hi all. If I try to embed a getpivotdata function into a subtotal function in excel 2007, I get a dialogue box saying "the formula you typed contains an error..." and I cannot enter the formula.

What I'm trying to do is as follows:


Cell Q9 has the following GETPIVOTDATA formula, which works just fine:
=GETPIVOTDATA("Revenue",'SKU month Pivot'!$A$13,"Order Month/Year",N$5,"Parent Product",$B9,"Sales_Channel","Education")

Cell P10 has the following SUBTOTAL formula, which points to the abovementioned GETPIVOTDATA function, which works fine:
=SUBTOTAL(9,Q9)

However, when I try to amalgamate these into one formula, I get the dialogue box saying "the formula you typed contains an error..." and I cannot enter the formula as below:

=SUBTOTAL(9,GETPIVOTDATA("Revenue",'SKU month Pivot'!$A$13,"Order Month/Year",N$5,"Parent Product",$B9,"Sales_Channel","Education"))

Looks like a bug in 2007 to me. Any thoughts, anyone?

 

[jeffreyweir]

I believe it's because unlike the SUM function which accepts numbers OR references, the SUBTOTAL function only accepts references (and I'm attempting to feed it a number via the GETPIVOTDATA function).

So I may have to have these functions in 2 different cells.

Any thoughts otherwise?