Excel 2010 - External Data Source for Pivot Tables

Hi, I have an Excel 2010 file that has multiple pivot tables and pivot charts on several tabs, all connected to a large table in another tab (called DataDump) in the same file. As I added to the DataDump table over the weeks, it has gotten very large 22mb, so decided to move the DataDump table to a seperate file that I would create an external data connection to. All seemed to be fine in that I was able to go to Data/Connections/Add and create an external data connection to this DataDump table file that resides on Sharepoint. Problem is that when I select the any of pivot tables, go to PivotTableTools/Options/Change Data Source the "use an external data source" is greyed out.

I even added the sharepoint site location for the DataDump file to my trusted center, but that didn't seem to make a difference. Any ideas on why this option is greyed out? Thanks in advance for your help!

Regards,

Rick

Rick

This is a guess, but I suspect that as you are already in the pivot table, as you are using the contextual menu, that it is not allowing a second connection for that table full stop.

What I would do is set up a connection in a new tab and then go to the data menu, connections and look at the connection string. You should then go, in the same area to the connection for your pivot table and paste this connection string in.

Hope that makes sense? (I have to say i am on a machine without 2010, but I think those options should work