Mynda, you can simplify complex things so easily! I had trouble understanding GETPIVOTDATA, but this tutorial made it clear. Thank you! 😊👍
@MyOnlineTrainingHub2 жыл бұрын
So pleased to hear that, Vijay 😊
@rhonskibeat12 жыл бұрын
Thank you so much for this video!!! I've spent hours trying to figure out how I can make my GETPIVOTDATA dynamic now that I am using powerpivot and this is the only video that have clearly explained a solution!! Thank you so much!!!!
@MyOnlineTrainingHub2 жыл бұрын
So pleased we could help 😊
@ultimoobrero89922 ай бұрын
Thank you much Mynda! Been having issue with my dashboard's headline, pivot data moves when I click on a slicer. Funny, all I needed was to enable that generate GetPivotData option. You really save the day(s).
@MyOnlineTrainingHub2 ай бұрын
Glad it was helpful!
@jetg21112 жыл бұрын
I love these tips... Things that seem almost obvious once they are explained so well, and you understand why... Thanks so much!
@MyOnlineTrainingHub2 жыл бұрын
Great to hear 😊
@florianmeyer6072 ай бұрын
Thanks! Was having so much trouble to find the right way to have this dynamic values for power query pivot tables! You helped a ton!
@MyOnlineTrainingHub2 ай бұрын
Great to hear!
@armaankhan51482 жыл бұрын
It is so nice i was doing manually in our monthly report you save my lot's of time. Thanks
@MyOnlineTrainingHub2 жыл бұрын
Glad to hear that, Armaan!
@iziaurrahman7 ай бұрын
I was stuck with this problem fur last couple of days. Now I know how to come around. Thanks a lot
@MyOnlineTrainingHub7 ай бұрын
Happy to help!
@starstick94852 жыл бұрын
I have been following your videos - Very helpful for me. I am a retired VP and manage my own stock portfolio. I have several sheets in my work book that use a column of stock symbols in separate tables. When I add or subtract a stock in say the master table I would like all of the tables to update with the new stock entry. I have tried many methods with no success - very frustrating for me. Note: I am not a pivot table user. Regards and thanks.
@MyOnlineTrainingHub2 жыл бұрын
Glad you find my videos helpful 😊 your source data would be better in a single table. You can use Power Query to automate the gathering of the data into a single table: www.myonlinetraininghub.com/power-query-consolidate-excel-sheets
@pxxgxx19492 жыл бұрын
Merry Christmas Mynda, Phil & team. Thanks so much for the year's tips and all the work you do. I'm looking forward to more in the New Year and wish you all the best for the years ahead. Stay safe xx
@MyOnlineTrainingHub2 жыл бұрын
Thanks so much! And to you 😊
@NicolasBORDE2 жыл бұрын
Great. I was looking for an easy way to get the values for the most recent date on some data... This is perfect !
@MyOnlineTrainingHub2 жыл бұрын
Glad you can make use of it 😊
@alparkson54942 жыл бұрын
Happy Holidays, Mynda! Thank you for all you do :)
@MyOnlineTrainingHub2 жыл бұрын
Thanks so much, Al! Happy holidays to you too 😊
@mook528 Жыл бұрын
THANK YOU FOR THIS!, I was just about to give up until I came across this video!
@MyOnlineTrainingHub Жыл бұрын
Glad I could help!
@wayneedmondson10652 жыл бұрын
Hi Mynda. What a super tip! So easy, once you demonstrate how it is done. Thanks for sharing :)) Thumbs up!!
@MyOnlineTrainingHub2 жыл бұрын
Cheers, Wayne!
@Muuip Жыл бұрын
Works great for stabilizing formating in charts!
@MyOnlineTrainingHub Жыл бұрын
Glad it's helpful!
@JonathanExcels2 жыл бұрын
I have been using the formula in the way you described. I just wish it was as easy as a regular pivot table.
@MyOnlineTrainingHub2 жыл бұрын
It’s not too bad once you’re used to it 😊
@ksumar2 жыл бұрын
Useful. I had actually switched this feature off as it now reads B3, B4 etc on = Cell ...as I didn't really appreciate this concept!
@MyOnlineTrainingHub2 жыл бұрын
Hope you’ll be using it going forward 😊
@raihansafa55147 ай бұрын
Thank you. This was so much needed.
@MyOnlineTrainingHub7 ай бұрын
Glad it was helpful!
@darrylmorgan2 жыл бұрын
Hi Mynda!Great Tutorial,Really Cool Tip To Make The Formula Dynamic...Thank You :)
@MyOnlineTrainingHub2 жыл бұрын
Cheers, Darryl!
@delroydemontagnac52422 жыл бұрын
This was so useful. Thank you
@MyOnlineTrainingHub2 жыл бұрын
Great to hear, Delroy!
@iankr2 жыл бұрын
Many thanks, Mynda.
@MyOnlineTrainingHub2 жыл бұрын
My pleasure, Ian!
@zubairso2 жыл бұрын
This is truly amazing! I really love the getpivot function.
@MyOnlineTrainingHub2 жыл бұрын
Great to hear!
@phongnguyenthanh90762 жыл бұрын
Amazing. Thank for these tips
@MyOnlineTrainingHub2 жыл бұрын
Glad you like them!
@lukasth_2 жыл бұрын
Love your videos! Saves so much time! 🙂 Thanks a ton!!
@MyOnlineTrainingHub2 жыл бұрын
Great to hear 😊
@Gaijindesune2 жыл бұрын
Great tips! Thank you for your clear explanations and the Canadian data shout-out!! Just wanted to point out that Saskatchewan is misspelled. Right now it says "Saskachewan" (you are missing the "T").
@MyOnlineTrainingHub2 жыл бұрын
Oops! Thanks for the correction. I can't remember where I got that data from.
@rob4canada2 жыл бұрын
Great video and very helpful. I tend to avoid pivot tables but you videos are making me rethink that stance. Shout out for Canadian data. :-)
@MyOnlineTrainingHub2 жыл бұрын
Thanks, Robert! Definitely embrace PivotTables 😊
@AllenMarkPongasi2 жыл бұрын
Nailed it! Thank you very much!
@MyOnlineTrainingHub2 жыл бұрын
Glad it helped!
@martinargimon7302 жыл бұрын
Excellent explanation!!! Thanks a lot !
@MyOnlineTrainingHub2 жыл бұрын
Glad you liked it :-)
@raylee33992 жыл бұрын
you save my life
@MyOnlineTrainingHub2 жыл бұрын
Glad we can help 😊
@mdexcelpro84952 жыл бұрын
Great as and valuable tips 👍
@MyOnlineTrainingHub2 жыл бұрын
Glad you liked it 😊
@misstoffeepenny8 ай бұрын
Hi this is a fab video, I finally understand the breakdown of this function. I'm wondering how I can get the data from the pivot table for year based on todays date?, where my row is year. any help appreciated
@MyOnlineTrainingHub8 ай бұрын
Can't say without seeing your file, but the easiest thing to do is type = in an empty cell and then click on the value in the PivotTable you want. This will show you the formula syntax and from there you can reconstruct it with cell references.
@davidburch8079 Жыл бұрын
Helpful video. Thanks :)
@MyOnlineTrainingHub Жыл бұрын
Great to hear!
@chrism90372 жыл бұрын
Great video thanks Mynda!
@MyOnlineTrainingHub2 жыл бұрын
Cheers, Chris!
@madelinemoisio74185 ай бұрын
Mynda, thank you for this amazing video! This was so helpful in understanding how to use getpivotdata for a data model table. I see that you showed us how to change the formula so that it was reading the months from a referenced cell, do you have any advice on doing that for a reference cell that contains a date? I keep getting #REF! when I try to replace a date with a cell that contains a date. I am following the syntax you shared. The source table has the date formatted as a date so I don't understand why the data model table does not have the value formatted that way.
@MyOnlineTrainingHub5 ай бұрын
When working with dates in PivotTables I always use my mouse to have Excel write the GETPIVOTDATA formula, then I can see the date structure it's expecting and make sure the cells I want to reference are in this same format.
@IlaPatel8112 жыл бұрын
Love it! Thanks for sharing!
@MyOnlineTrainingHub2 жыл бұрын
You are so welcome!
@rglugo182 жыл бұрын
Very helpful video. Thanks
@MyOnlineTrainingHub2 жыл бұрын
Glad you liked it 😊
@almighty.rex.110 ай бұрын
Do you have a video on how to pull values from a PowerPivot table into another sheet in the same workbook? I have my powerpivot table set up but have another sheet that I would like to pull values from one of the columns into another sheet. I’ve tried everything.
@MyOnlineTrainingHub10 ай бұрын
The only way is to load the data via a Power Pivot PivotTable. If you put all the columns from the Table in the PivotTable, it will show you all the data. Change the layout to 'Tabular' and remove subtotals and grand totals, and you have yourself a table.
@Gary-ln3td Жыл бұрын
Thanks for the video. Is there a way to create a normal pivot table from the PowerQuery data, and then use the getpivotdata formula? I ask because the formula is much cleaner to make dynamic in various ways and also user friendly to read/understand that way, compared to the beastly PowerQuery getpivotdata formula.
@MyOnlineTrainingHub Жыл бұрын
Yes, you can use GETPIVOTDATA with regular PivotTables as explained here: kzbin.info/www/bejne/gnXJkIKIo7h2q5Y
@ajitsinghjasdhol73922 жыл бұрын
Thank you very much
@MyOnlineTrainingHub2 жыл бұрын
My pleasure 😊
@imdtaurusАй бұрын
amazing thank you so much
@MyOnlineTrainingHubАй бұрын
You're very welcome!
@ze00002 жыл бұрын
Great! Can you make some videos about cubo functions? Thanks
@MyOnlineTrainingHub2 жыл бұрын
I’ll keep it in mind 👍
@mehmetakarca64008 ай бұрын
Thank you. I have a question. How can I use this to get value under the date format?
@MyOnlineTrainingHub8 ай бұрын
It depends on whether you're summarising the data by day, month, quarter, year etc. However, if you type = and then click on the value field you want, you'll see the syntax required for the date that value field relates to and from there you can modify the formula to refer to dynamic cells.
@johannessheriff50662 жыл бұрын
Helpful, Great work.
@MyOnlineTrainingHub2 жыл бұрын
Thanks so much 😊
@shoppersdream7 ай бұрын
Thanks, Minda! Very Nice! Can you please help me with this issue? Somebody created a very complicated Pivot Table and I am trying to understand that Pivot Table. I added a custom field as Subtotal in the PT( total of 4 columns-ColumnName4) + ( total of 3 columns-ColumnName3). I used PowerPivot and Data Model and now Custom Field has become a separate Excel table and I want it to become a part of the existing Pivot table. (None of the fields match with the Data Source because there are a lot of aggregate functions used and renamed columns) Dealing with this Data Source is a nightmare. In your example, this was not a simple Pivot Table. Please help me. I just want to show the Sum of 2 Columns from PT and want that custom field to become a part of this PT. Thanks
@MyOnlineTrainingHub7 ай бұрын
That sounds very odd. Please post your question and sample Excel file on our forum where someone can help you further: www.myonlinetraininghub.com/excel-forum
@edsta7142 жыл бұрын
Wouldn’t Cube formulas be better if it’s in the data model?
@jeremylau932 жыл бұрын
I think so. I usually use cube function instead of getpivotdata function.
@chiquita6832 жыл бұрын
Yea it would be good to show why/when it's better to use this vs the cube formula. I assume they each have different uses, I tend to just use cube formulas all the time
@MyOnlineTrainingHub2 жыл бұрын
Cube formulas definitely have advantages over GETPIVOTDATA, but they’re also a bit harder to learn.
@shakthiatukorala5952 жыл бұрын
Great..! I like this tutorial..
@MyOnlineTrainingHub2 жыл бұрын
Great to hear 😊
@richardsimmonds65926 ай бұрын
Hi Mynda, should this work if i have a pivottable directly sourcing data from a Power Bi dataset? I cant seem to get it to work, despite when hovering over a month i am trying to reference to from a cell the yellow formula preview renders correctly but the formula still results in a #REF! error.
@MyOnlineTrainingHub6 ай бұрын
Not sure what you mean by 'the yellow formula preview'. You should be able to type = and then click on the value field in the PivotTable to get the GETPIVOTDATA formula to populate. From there you can edit the formula. If you're still stuck, you're welcome post your question and sample Excel file/screenshots on our forum where someone can help you further: www.myonlinetraininghub.com/excel-forum
@abdomicron23672 жыл бұрын
i love that really helped me :D
@MyOnlineTrainingHub2 жыл бұрын
Great to hear!
@ahchan22 жыл бұрын
Thank you for the Video, may I know how can I reference a "range" into the argument? I would like to run a Max array function and return the value in this case
@MyOnlineTrainingHub2 жыл бұрын
Please post your question and sample Excel file on our forum where we can help you further: www.myonlinetraininghub.com/excel-forum
@AbirBaidya2 жыл бұрын
Thanks...
@MyOnlineTrainingHub2 жыл бұрын
Our pleasure, Abir!
@kayalha2 жыл бұрын
Helpful 💯
@MyOnlineTrainingHub2 жыл бұрын
Glad you liked it 😊
@mihaelastancu49242 жыл бұрын
Hi Mynda, I have a technical question :) and I hope you'll help me clear it out: could you please tell me what kind of audio setup you're using for recording your videos? Microphone, recorder, any other important ingredient for obtaining such a wonderful clarity of your recorded sound? Something specific to pay attention to, when going out shopping for this kind of equipment? Any advice on that? :) Thank you very much in advance!
@MyOnlineTrainingHub2 жыл бұрын
Hi Mihaela, I'm using a Samson G-Track microphone and I use Camtasia Studio to record my videos. Hope that helps :-)
@mihaelastancu49242 жыл бұрын
@@MyOnlineTrainingHub Yes, thank you very much!
@valli61742 жыл бұрын
Hi, could you possible do a dividend sheet, where you could monitor dividends automatically from a dashboard? I´ve been trying to create a dashboard like that to better monitor my stocks, how much I will get, when I will get it and so on, but I don´t know how to implement the data automatically.
@MyOnlineTrainingHub2 жыл бұрын
I don't know of a single reliable source of dividend data that will allow you to automate the gathering of this information.
@mrunalmokal12962 жыл бұрын
Hi I have a requirement where in i have to enter getpivot formula multiple time for set of combination. E.g.using the pivot shown in video In cell i want to have data for 3 items from row and 3 items from columns but in one cell. Is there any easy way. Since i have to enter i have to manual copy paste the formula in cell and then change the range to get correct data in particular cell. Its very tds.
@MyOnlineTrainingHub2 жыл бұрын
Not using GETPIVOTDATA, no.
@dat1dud336 Жыл бұрын
Is there a way to exclude Nov and December if you choose Newfoundland?
@MyOnlineTrainingHub Жыл бұрын
Sure, you could wrap the formula in an IF that checks if the result = 0 and then return blank if it does.
@anasabdullah1799 Жыл бұрын
Hello, can I reduce the informatiob that shown after drilling the pivot table, for ex, I have a table with name, age, gender, and I insert a pivot table and I want to show only name, and age after drill down the result in the pivot. Thank you.
@MyOnlineTrainingHub Жыл бұрын
Maybe. It's difficult to visualise what you mean. Please post your question and sample Excel file on our forum where we can help you further: www.myonlinetraininghub.com/excel-forum
@republikadugave4202 жыл бұрын
Can you make this dynamic for a normal pivot table?
@MyOnlineTrainingHub2 жыл бұрын
Yes. See my GETPIVOTDATA for regular PivotTables tutorial: www.myonlinetraininghub.com/excel-getpivotdata-function
@k-mark91872 жыл бұрын
Can you reference the field though? YOu are only making the item dynamic
@MyOnlineTrainingHub2 жыл бұрын
You can reference any value area cell. If you want to query the data model directly, then check out CUBE functions: kzbin.info/www/bejne/jafEm6SwbNp1mNU
@anildkumble49884 ай бұрын
Can you please make a video describing - how to make date dynamic -in power pivot - pivot table. Date part of formula says [GetPivotData_PowerPivot].[Date]","[GetPivotData_PowerPivot].[Date].&[2024-04-02T00:00:00]" If you can make a video for us, it will be very helpful. Regards
@MyOnlineTrainingHub4 ай бұрын
You can modify the formula like so where the date is in cell C4: [GetPivotData_PowerPivot].[Date]","[GetPivotData_PowerPivot].[Date].&["&TEXT(C4,"yyyy-mm-dd")&"T00:00:00]")
@PaulRichards12 жыл бұрын
Can you make a video that shows how to deal with balance sheet vs income statement variables in your data set? For example, let's say I have monthly data for both my IS and BS but I want to show total 2021 in a pivot table. I want the pivot table to add up all the IS variables, but I only want the BS variable to show the YE 21 ending value, not the sum of every balance from every month of the year. Is there a way to do that?
@MyOnlineTrainingHub2 жыл бұрын
It's tricky with PivotTables to segregate data like that. You might be best to use CUBE functions or GETPIVOTDATA to build your reports so you can pick and choose the figures you want in your reports.
@PaulRichards12 жыл бұрын
@@MyOnlineTrainingHub Thanks, I've been wondering if I'm missing something obvious.
@MyOnlineTrainingHub2 жыл бұрын
You can change the BS calculation to only display the current period, thus only returning the sum of one value.
@peterh3213 Жыл бұрын
you're a lifesaver! thank you!
@MyOnlineTrainingHub Жыл бұрын
Glad it helped!
@dunghuynhchi82292 жыл бұрын
Love you and your videos Mynda 🥹. Thank you so much!!!