How to Use "GetPivotData" formula to retrieve data from a closed workbook

[kizzie37]

I have an excel workbook "Master Database" which contains a "PIVOT" tab with 4 pivot tables of info.

I then have a workbook with multiple tabs all of which have tables using the "Getpivotdata" formula to look up and bring back financial/count info from the master file for each employee for each month (I set up a connection "from excel file"). The problem is it only works if I have both the target workbook and the master workbook open at the same time.

What I need is for the master file to sit in one folder and the target files to sit on the same shared drive but in a different folder and still bring back the formula result without the master database workbook being opened each time.

I've researched various connection options but cant pin down the exact way to do this. Can anyone assist?

 

[RoryA]

GETPIVOTDATA will only work if the source workbook is open. You could probably do something with power query to pull in the sheet with the pivot table on it as a table, and manipulate that. Otherwise you'd probably have to recreate the GETPIVOTDATA formulas in the master workbook and then link to those with regular formulas, but that seems like a lot of overhead.

 

[kizzie37]

GETPIVOTDATA will only work if the source workbook is open. You could probably do something with power query to pull in the sheet with the pivot table on it as a table, and manipulate that. Otherwise you'd probably have to recreate the GETPIVOTDATA formulas in the master workbook and then link to those with regular formulas, but that seems like a lot of overhead.

Thanks, do you have any instructions on how to set up a power query? the only instructions i could find were for direct copy and paste from a closed workbook but I actually need to use GetPivots data or lookups.

 

[RoryA]

You can't use Getpivotdata. You would have to use PQ to return all the values on the sheet as a table, then use other formulas (lookup types probably) to extract the data you need from that table.