Help with GETPIVOTDATA #REF error

[ExcelAtEverything]

Pulling my hair out trying to figure out why this just pulls a #REF error instead of actually working. This formula is supposed to show the Avg Unit Price by dividing the monthly Gross Sales (GS$) by # of total items sold so far this month (GS Monthly). I know that it's the GETPIVOTDATA part that's not working correctly, but I haven't been able to figure out how to repair it.

Could someone help me to understand why this doesn't work? I used the same formula elsewhere in the "yesterday" section of the spreadsheet (out of view from the screenshot below), and it works flawlessly. The only change is the word "Yest" is changed to Monthly for this formula, because that Pivot Table is on a different tab. For this, thegreen/white table below is called "Month_To_Date". The Pivot Table below used in this formula is located on the "Item Totals Monthly" tab in a table called "Item_Tot_Monthly".

Thanks!

=IFERROR([@[GS$]]/GETPIVOTDATA("[Measures].[Sum of Quantity]",'Item Totals Monthly'!$B$3,"[Monthly Orders Reports].[Org #]","[Monthly Orders Reports].[Org #].&[" & [@[Org '#]] & "]"),"-")

 

[RoryA]

I'm not sure how you're seeing a #REF error with that IFERROR in place.

 

[ExcelAtEverything]

Hi RoryA! Yes, you're correct, my apologies. I removed the IFERROR part while I was working on trying to figure it out, and I guess I mistakenly took the screenshot after I replaced it rather than before. Sorry about that! So to sum up, if you remove the IFERROR portion, it gives a #REF error.

 

[RoryA]

If you create a static getpivotdata formula for that cell, what does it look like?

 

[ExcelAtEverything]

If you create a static getpivotdata formula for that cell, what does it look like?

=GETPIVOTDATA("[Measures].[Sum of Quantity]",'Item Totals Monthly'!$B$3,"[Monthly Orders Reports].[Org #]","[Monthly Orders Reports].[Org #].&[237]","[Monthly Orders Reports].[Date (Month)]","[Monthly Orders Reports].[Date (Month)].&[Apr]","[Monthly Orders Reports].[Date (Year)]","[Monthly Orders Reports].[Date (Year)].&[2021]")

 

[RoryA]

As you can see, you are missing a lot of required field info in your original version.

 

[ExcelAtEverything]

Definitely. So... I was trying to make it so that when I dragged the formula down, it translated correctly. I also needed to make the Org # "237" dynamic. The parts that I pasted in worked in the original formulas that I got them from (courtesy of the awesome mart37 on MrExcel). But there is ceertainly the possibility that I'm doing it incorrectly, hence why I asked for the assistance. I can't seem to get these correct. I moved them to where they made sense to me, for what I believe they are doing. I made those transitions in several other columns correctly after going through a few attempts. Just can't seem to get this one & feel like I'm way off.

 

[ExcelAtEverything]

I've posted several of these for the columns I can't get right on my own, and mart37 has been grabbing almost all of them and hooking me up. This is one that I tried on my own based on some of his formulas. If that makes sense I hope. Lol

 

[RoryA]

Assuming the Org number is what changes as you drag down, it would be:

=GETPIVOTDATA("[Measures].[Sum of Quantity]",'Item Totals Monthly'!$B$3,"[Monthly Orders Reports].[Org #]","[Monthly Orders Reports].[Org #].&[" & [@[Org '#]] & "]”,"[Monthly Orders Reports].[Date (Month)]","[Monthly Orders Reports].[Date (Month)].&[Apr]","[Monthly Orders Reports].[Date (Year)]","[Monthly Orders Reports].[Date (Year)].&[2021]")

 

[ExcelAtEverything]

Sorry if this is a dumb question, but what about the date? What about when 2022 arrives?