ExcelAtEverything
Active Member
- Joined
- Jan 30, 2021
- Messages
- 351
- Office Version
- 2019
- Platform
- Windows
Hello,
In the formula below, the 2nd line ([Date]) is causing me to receive a #REF error. In the part immediately after TEXT, F$5 refers to a date formatted cell showing the date "Monday, March 21, 2021", and the reason I'm subtracting -365 from it is because I want to reference the info from the same day last year.
=GETPIVOTDATA("[__Xl2].[Measures].[Sum of Line Item Total 3]",'LY GS & GP Daily-MTD'!$B$3,
"[LY Orders Report 2].[Date]","[LY Orders Report 2].[Date].&["&TEXT(F$5-365,"yyyy-mm-dd")&"01T00:00:00]",
"[LY Orders Report 2].[Org #]","[LY Orders Report 2].[Org #].&["&$C8&"]")
Starting with the static version of the cell reference below, I removed lines 3 ([Month]) and 4 ([Year]) because I don't need them. To be sure, I tried adding them back in, but didn't make any difference.
=GETPIVOTDATA("[Measures].[Sum of Line Profit 2]",'LY GS & GP Daily-MTD'!$B$3,
"[LY Orders Report 2].[Date]","[LY Orders Report 2].[Date].&[2020-06-01T00:00:00]",
"[LY Orders Report 2].[Date (Month)]","[LY Orders Report 2].[Date (Month)].&[Jun]",
"[LY Orders Report 2].[Date (Year)]","[LY Orders Report 2].[Date (Year)].&[2020]",
"[LY Orders Report 2].[Org #]","[LY Orders Report 2].[Org #].&[237]")
Thanks.
In the formula below, the 2nd line ([Date]) is causing me to receive a #REF error. In the part immediately after TEXT, F$5 refers to a date formatted cell showing the date "Monday, March 21, 2021", and the reason I'm subtracting -365 from it is because I want to reference the info from the same day last year.
=GETPIVOTDATA("[__Xl2].[Measures].[Sum of Line Item Total 3]",'LY GS & GP Daily-MTD'!$B$3,
"[LY Orders Report 2].[Date]","[LY Orders Report 2].[Date].&["&TEXT(F$5-365,"yyyy-mm-dd")&"01T00:00:00]",
"[LY Orders Report 2].[Org #]","[LY Orders Report 2].[Org #].&["&$C8&"]")
Starting with the static version of the cell reference below, I removed lines 3 ([Month]) and 4 ([Year]) because I don't need them. To be sure, I tried adding them back in, but didn't make any difference.
=GETPIVOTDATA("[Measures].[Sum of Line Profit 2]",'LY GS & GP Daily-MTD'!$B$3,
"[LY Orders Report 2].[Date]","[LY Orders Report 2].[Date].&[2020-06-01T00:00:00]",
"[LY Orders Report 2].[Date (Month)]","[LY Orders Report 2].[Date (Month)].&[Jun]",
"[LY Orders Report 2].[Date (Year)]","[LY Orders Report 2].[Date (Year)].&[2020]",
"[LY Orders Report 2].[Org #]","[LY Orders Report 2].[Org #].&[237]")
Thanks.