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 '#]] & "]"),"-")

 

[ExcelAtEverything]

Also, when I put that in, I get this error:

And then when I "x" out of that, I see this:

 

[ExcelAtEverything]

Hey Rory, I recognized that pattern of info around the Org # in your formula, and I replaced a small section of your formula with a portion of mart37's code (the correction is highlighted below), and now it works! Still trying to figure out the date portion to get rid of the April & 2021 references.

 

[RoryA]

The date is at the end. Remember we don’t have your workbook or a clear indication so far of exactly what data you are trying to return.

 

[RoryA]

Sorry, it looks like my iPad changed one of the quotes to curly quotes rather than regular ones.

 

[ExcelAtEverything]

I totally understand. Trust me, you were the help I needed. Whatever you did toward the beinning was the fix that I couldn't figure out. I just used mart37's time/date fix as well, and now it works perfectly! Here is the final formula which is now working! Thx RoryA! ?

=IFERROR([@[GS$]]/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)].&["&TEXT(TODAY()-1,"mmm")&"]"),"=")

 

[ExcelAtEverything]

Rory, knowing that you are a moderator I have a question here. I want to checkmark your formula, even though it had an error and needed a couple of adjustments. You had limited info though, so I know how that goes. But that was still the basics of what led to the correct answer. Should I check your box? Or should you reply back with the ending formula so I can check the actual real correct formula? Or do I not check anything? What's the best thing to do in this scenario?

 

[ExcelAtEverything]

Sorry, it looks like my iPad changed one of the quotes to curly quotes rather than regular ones.

All this talk of curly quotes is making me hungry!?

 

[RoryA]

Since you've posted the correct formula, you can just select that as the answer so that anyone coming to this thread later knows what to look at.

 

[ExcelAtEverything]

Since you've posted the correct formula, you can just select that as the answer so that anyone coming to this thread later knows what to look at.

?