GETPIVOTDATA Syntax error possibly invoving slicer selection

ExcelAtEverything

Active Member
Joined
Jan 30, 2021
Messages
351
Office Version
  1. 2019
Platform
  1. 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:
"[Monthly Orders Reports].[Org #]","[Monthly Orders Reports].[Org #].&[237]")
What I changed it to (which is throwing the syntax error) is:
1620017562204.png
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:
1620018412706.png

1620017299919.png


My previous thread on this topic: GETPIVOTDATA Syntax Error
 

Attachments

  • 1620015093528.png
    1620015093528.png
    66.8 KB · Views: 10

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.
I just realized that I don't have any of the other lines that I already changed to be relative, set up with their proper sheet/table names yet either. I'm fixing that right now, but I don't think that is the issue, since after throwing the syntax error, Excel highlighed the Org row.
 
Upvote 0
What happened was twofold. Part of it was that I didn't have the other lines set up with their exact sheet names yet when I asked this question. And secondly, in the pivot table I had mistakenly dragged the Org #data into the "row" section instead of the "filter" section. Fixed. Oops!
 
Upvote 0
Solution

Forum statistics

Threads
1,223,908
Messages
6,175,306
Members
452,633
Latest member
DougMo

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top