I have the following formula that is returning the correct value:
cell 'ODS TTM Coverage'!N12 = GETPIVOTDATA("[Measures].[Amt]",'ODS TTM Coverage'!$A$5,"[Time].[Calendar]","[Time].[Calendar].[Year].&[2017].&[12]&","[Client].[Client.Name].&[M1 Tech]","[Coverage].[CoverageDescription]","[Coverage].[Coverage Description].&[Fee]")
I am currently using the formula :
=sum('ODS TTM Coverage'!N12:Offset('ODS TTM Coverage'!N12,0,-12)))
however, I would like to replace the reference to cell N12 with the Getpivotdata reference but Excel doesn't like that.
The reason I want to do this is because I need the sum (offset ) formula to be a bit more dynamic. I need to use 3 different criteria (time, client name and coverage) to find my starting point and then always sum 12 months prior. The range will also be dynamic so that's why I wanted to use the getpivotdata formula as opposed to needing to create and array formula..
thanks
cell 'ODS TTM Coverage'!N12 = GETPIVOTDATA("[Measures].[Amt]",'ODS TTM Coverage'!$A$5,"[Time].[Calendar]","[Time].[Calendar].[Year].&[2017].&[12]&","[Client].[Client.Name].&[M1 Tech]","[Coverage].[CoverageDescription]","[Coverage].[Coverage Description].&[Fee]")
I am currently using the formula :
=sum('ODS TTM Coverage'!N12:Offset('ODS TTM Coverage'!N12,0,-12)))
however, I would like to replace the reference to cell N12 with the Getpivotdata reference but Excel doesn't like that.
The reason I want to do this is because I need the sum (offset ) formula to be a bit more dynamic. I need to use 3 different criteria (time, client name and coverage) to find my starting point and then always sum 12 months prior. The range will also be dynamic so that's why I wanted to use the getpivotdata formula as opposed to needing to create and array formula..
thanks
