Pivots based on external data sources

[iflaneur]

Hello,

Every week I receive a report (always 16 columns, same file name, new rows added every week) that I analyse using a pivot table, using the same filtering set up every week.

In Excel 2010, I want to build a "Master Pivot" that will refresh automatically with the new data.

To do so I have:
- created a folder to store the weekly report. Eg. C:\Reports\
- created a Named Range (eg. 'RAWDATA') that is defined dynamically (I have tried volitile and non-volitile functions, OFFSET and INDEX)

I have defined the named range RAWDATA both​

• ='C:\Reports\[Sample report.xlsx]Sheet1!$A$1:INDEX('C:\Reports\[Sample report.xlsx]Sheet1'!$A:$P,COUNTA('C:\Reports\[Sample report.xlsx]Sheet1'!$A:$A),16)
  ​
• =OFFSET('C:\Reports\[Sample report.xlsx]Sheet1'!$A$1,0,0,COUNTA('C:\Reports\[Sample report.xlsx]Sheet1'!$A:$A),16)
  ​

- created a Pivot based on that Named range

PivotTable data source: 'C:\Reports\Sample report.xlsx'!RAWDATA​

This works if each week I copy-paste the new rows to Sample report.xlsx.

This doesn't work if I replace Sample report.xlsx with the new file that comes through each week.

Can I make it work?

Thanks a lot for your help!
Daniele