GETPIVOTDATA Dates Reference Returning #REF! Error

**Jake32008** · 03-09-2015, 12:50 PM

I am having trouble with the following formula

=GETPIVOTDATA("[Measures].[Sum of Forecast]",'Sales Forecast'!$E$3,"[T_Sales_Forecast].[Forecast Month]","[T_Sales_Forecast].[Forecast Month].&["&TEXT(G$7,"YYYYMM")&"]")

When I check if G$7 = the date in the pivot table (2/1/2015) the result is TRUE, however, text("G$7, "YYYYMM") = 2/1/2015 evaluates to false. Therefore I've tried changing the formula above to the following but still receive an error

=GETPIVOTDATA("[Measures].[Sum of Forecast]",'Sales Forecast'!$E$3,"[T_Sales_Forecast].[Forecast Month]","[T_Sales_Forecast].[Forecast Month].&["&G$7&"]")

Thanks in advance for your help.

**simarui** · 03-09-2015, 12:55 PM

this would be easier to check if you uploaded a sample of your workbook, but i think you can delete all of the "[T_Sales_Forecast].[Forecast Month].&[" noise so the last field should just be ,G$7)

i.e.

Formula:

Please Login or Register  to view this content.

**Jake32008** · 03-09-2015, 01:19 PM

Thanks for the response. I removed the noise and still same result.

I'm trying to create a sample sheet with the error as there is proprietary info in the original as you can imagine.

**Jake32008** · 03-09-2015, 01:31 PM

Please refer to the attached sheet with adulterated data

**simarui** · 03-09-2015, 02:13 PM

that's odd... when i open your file i see the reference error, but when i click into the formula editor and hit return it returns 78532 (the grand total for February as expected...)