ExcelAtEverything
Active Member
- Joined
- Jan 30, 2021
- Messages
- 351
- Office Version
- 2019
- Platform
- Windows
Hello,
I can't seem to change the static reference in the last line which pertains to "Org #. so that it changes along with the slicer selection (which may be one or all of the "Org's").
The slicer (on right side of screenshot) is connected to the same pivot table that this formula is looking at (PrimCat TY...), but I keep getting a syntax error. I'm trying to change the line so that it remains consistent w/ the selected Org #, but I keep getting a syntax error.
RoryA helped me w/ a similar issue a few days ago (similar but with no slicer involved), where he pointed out that if the "Org" part of GETPIVOT formula is located in a separate table, then I must name that table in the GETPIVOT formula, so this is likely to be somewhat the same issue. (I placed a link to that conversation at the very bottom in case anyone would like to see that). But I can't figure out how to accomplish that when the Org # is derived from a slicer setting. I guess I could just do it using R1C1, but I would much prefer to not do that here.
Excel assigned the slicer formula names as Slicer_Org (for the green TY slicer), and Slicer_Org1 (for the gold LY slicer).
The unchanged last line of the formula in question is:
The highlighted part above([@Org[Org) is what the syntax error is pointing to.
Thanks for any help. Also, here's a quick shot of the Pivot Table in case that is relevant:
My previous thread on this topic: GETPIVOTDATA Syntax Error
I can't seem to change the static reference in the last line which pertains to "Org #. so that it changes along with the slicer selection (which may be one or all of the "Org's").
The slicer (on right side of screenshot) is connected to the same pivot table that this formula is looking at (PrimCat TY...), but I keep getting a syntax error. I'm trying to change the line so that it remains consistent w/ the selected Org #, but I keep getting a syntax error.
RoryA helped me w/ a similar issue a few days ago (similar but with no slicer involved), where he pointed out that if the "Org" part of GETPIVOT formula is located in a separate table, then I must name that table in the GETPIVOT formula, so this is likely to be somewhat the same issue. (I placed a link to that conversation at the very bottom in case anyone would like to see that). But I can't figure out how to accomplish that when the Org # is derived from a slicer setting. I guess I could just do it using R1C1, but I would much prefer to not do that here.
Excel assigned the slicer formula names as Slicer_Org (for the green TY slicer), and Slicer_Org1 (for the gold LY slicer).
The unchanged last line of the formula in question is:
"[Monthly Orders Reports].[Org #]","[Monthly Orders Reports].[Org #].&[237]")
What I changed it to (which is throwing the syntax error) is: Thanks for any help. Also, here's a quick shot of the Pivot Table in case that is relevant:
My previous thread on this topic: GETPIVOTDATA Syntax Error