Pivot Table Data Source from External Table

[rob1963manwork]

Hello!

• When i create a Pivot Table from an Excel Table, the source is defined as: "Table1" if the Table and the Pivot are located in the same workbook.
• When I create a Pivot Table from an Excel Table located in another workbook the same source is defined as: [Book1]Sheet1!$A$2:$N$6

Is there a way to define the range based on the Table's name when the pivot table is in another workbook? I've run into the situation that once the table grows the Pivot will not auto-adjust.

I knew enough to figure it out, but I'm not sure if there's a way to have the external pivot use the table name!

Thanks in advance for any insight!!

Rob

[Pepe Le Mokko]

Perhaps make your range on the other workbook an Excel table and use it as your Data source?

[rob1963manwork]

Perhaps make your range on the other workbook an Excel table and use it as your Data source?

Hi Pepe!!

The range on the other workbook is already a table. It's called Table1. The Pivot Table on the 2nd workbook is just not picking that up and doing an absolute cell reference instead.

[romperstomper]

I'd suggest you use Power Query to connect to the other workbook (you can reference the table that way) and then build your pivot off the query.

[Haluk]

As @rorya has suggested, PQ can be used to achieve the goal.

In addition to PQ, MS Query can be used as well to fetch the data from the other workbook and dump it on the other workbook as a Pivot Table. When the source data changes, all you need to do will be just a refresh on the pivot table.


\1