Anyone familiar with GETPIVOTDATA? Specifically: 1) any way to pull data by referencing a date without having to depend on the way the date is formatted and 2) any knowledge of how efficient it is as a formula (vs. doing an INDEX-MATCH or other approach)?
Context: I’ve added a couple of tabs in my Foundation template to create an actual spend vs. budgeted spend analysis (similar to, but slightly different from the native Yearly Budget). To pull in actual historicals, I’ve got my entire transaction history pulled into a pivot table. I’m then using GETPIVOTDATA to aggregate spending by category by month. The formula I’m using to pull the data out into my actual-vs-budget comparison tab is: =getpivotdata(“Sum of amount”,‘Transactions Pivot’!$A$1,“Category”,$B7,“Month”,text(E$2,“m/d/yy”)). The issue is that if any transaction in my Transactions sheet somehow ends up with a date that not formatted m/d/yy then the whole setup won’t work correctly. So that’s what’s behind my first question. Second question is just whether this entire infrastructure could be done more efficiently. My sheet got pretty slow after adding this setup.