=GETPIVOTDATA('Sum of MTD',PivotTableIncomeDivison,'Year','FY16','Month','July','Divison','Automotive') The three individual PivotTables are necessary, because of limitations with the GETPIVOTDATA function and what is being selected as Filters, Rows and Columns in each PivotTable. We will create a connection to that table in Access and then we will have three separate PivotTables using that one Access connection. However, we will have many more PivotTables in the workbook than three.įor example, let's call one of those Access tables 'Income Statement Analysis'. We will at mostĬreate three Access table connections. He uses Access to store the data and then pulls it into Excel as a PivotTable with Access connections. Now for my challenge, my boss has been using Access and PivotTables for many years and I believe probably for over a decade. Prior to dealing with PivotTables I relied heavily on a combination of VLOOKUP, SUMIFS, I do know how to use a PivotTable to select filters, rows, columns and then using the GETPIVOTDATA function. Until recently PivotTables were completely new to me. Access is still relatively new to me, but I've caught on quickly and am able to move get around decent enough using Queries and VBA code. Of VBA coding when it comes to writing macros in Excel and Access. I have a pretty strong grasp of excel formulas and a decent grasp I work in finance with a degree in accounting.

First off, let me apologize in advance, because I have limited experience with Access, PivotTables and no experience with PowerPivot.

Excel getpivotdata data model