Adjusting GETPIVOTDATA Formula To Be Dynamic

ExcelAtEverything

Active Member
Joined
Jan 30, 2021
Messages
322
Office Version
  1. 2019
Platform
  1. Windows
I am on the Year To Date (YTD tab now, and stuck on the same thing When I grab the static formula for the Pivot Table position, I need to adjust the Org # portion so the formula can be pulled downward and will translate properly to the diffferent Org #'s, and same holds true for date needing to be able to be pulled to right, as well as account for the fact that next year will be a new year. I'm trying very hard, but I'm having a tough time trying to grasp certain aspects of the GETPIVOTDATA function. Any help here greatly appreciated as always.

Green/White table w/ blacked out names: this table (named "YTD_GS") is the main sheet where the GETPIVOTDATA formulas go. Sheet name is "Main".
Black/white Pivot Table:

If I just grab the static Power Pivot data cell, the formula looks like this: =GETPIVOTDATA("[Measures].[Sum of Line Item Total]",'YTD GS'!$B$3,"[Monthly Orders Reports].[Org #]","[Monthly Orders Reports].[Org #].&[237]","[Monthly Orders Reports].[Date (Year)]","[Monthly Orders Reports].[Date (Year)].&[2021]")
And it fills down incorrectly like this.
1617511134110.png

.
1617511387388.png

I tried editing it myself and came up with this, which seems right to me, but it only throws #REF errors.
=GETPIVOTDATA("[Measures].[Sum of Line Item Total]",'YTD GS'!$B$3,"[Monthly Orders Reports].[Org #]","[Monthly Orders Reports].[Org #].&["&[@[Org '#]]&"]","[Monthly Orders Reports].[Date (Year)]","[Monthly Orders Reports].[Date (Month)].&["&TEXT(TODAY()-1,"mmm")&"]")

Thanks!
 

ExcelAtEverything

Active Member
Joined
Jan 30, 2021
Messages
322
Office Version
  1. 2019
Platform
  1. Windows
Are you saying that you think I need Power BI in order to do what I'm asking?
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

ExcelAtEverything

Active Member
Joined
Jan 30, 2021
Messages
322
Office Version
  1. 2019
Platform
  1. Windows
If that's the case, then I will need to abort the manual-entry date process, and revert back to the =today() method.
 

Alex Blakenburg

Well-known Member
Joined
Feb 23, 2021
Messages
1,752
Office Version
  1. 365
Platform
  1. Windows
Are you saying that you think I need Power BI in order to do what I'm asking?

Not at all. Only that the experts in using the Data Model will most likely also be the Power BI experts. I don't know if they would look at both the Excel Questions Forum AND the Power BI Forum. Just give it a day or so and see if anyone responds.
 

ExcelAtEverything

Active Member
Joined
Jan 30, 2021
Messages
322
Office Version
  1. 2019
Platform
  1. Windows

ExcelAtEverything

Active Member
Joined
Jan 30, 2021
Messages
322
Office Version
  1. 2019
Platform
  1. Windows
Alex, I just wanted to say that the advice you gave me in this thread regarding Pivot Tables and the GetPivotData formula is absolutely invaluable. I can't even tell you how often I have referred back to it over the last several days. From that alone (specifically the part about separating the formula onto different lines, and just replacing parts of it. Just superb. This thread, along with an exceptiponal 2hr video I found on Pivot Tables, has made me SO much better of the past few days. I'm having a blast rewriting these formulas, and making it work. Thanks so much for taking the time.
 

Alex Blakenburg

Well-known Member
Joined
Feb 23, 2021
Messages
1,752
Office Version
  1. 365
Platform
  1. Windows
Alex, I just wanted to say that the advice you gave me in this thread regarding Pivot Tables and the GetPivotData formula is absolutely invaluable. I can't even tell you how often I have referred back to it over the last several days. From that alone (specifically the part about separating the formula onto different lines, and just replacing parts of it. Just superb. This thread, along with an exceptiponal 2hr video I found on Pivot Tables, has made me SO much better of the past few days. I'm having a blast rewriting these formulas, and making it work. Thanks so much for taking the time.

You've made my day. Thank you.
 

ExcelAtEverything

Active Member
Joined
Jan 30, 2021
Messages
322
Office Version
  1. 2019
Platform
  1. Windows
Bro.... Stop it. You should make a tutorial. So many helpful and incredibly knowledgable people on this site, and so many have helped me to understand things when I get confused. But the way which you explain things is on a different level. I went from needing help with so many questions each day, and I was so lost in that formula. By the next day around noon, I felt like a whiz at it. For as long as it took me to get all of that help from people that I requested regarding PT's and that GETPIVOTDATA formula, I ended up erasing all of it and redoing the, all myself, simply because I couldn't be sure they were 100% correct if someone else did them. So instead of going back and analyzing everyone's formula, I decided to redo every single cell myself. I now feel so much more in control of the project.

Ok, I gotta stop now before your head gets too big. But I'm serious man. You have no idea how much you completely cleaned the cobwebs for me. I'm sure I'll need a lot more help still, but I feel so much more confident that the sheet has not gotten away from me at all, whereas I totally felt like that before.

Thx Alex. You have a gift when it comes to teaching others.
 

Forum statistics

Threads
1,141,715
Messages
5,708,058
Members
421,541
Latest member
Akidev

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
Top