Pivot Table Data Source

wildturkey

Board Regular
Joined
Feb 21, 2006
Messages
189
Office Version
  1. 365
Platform
  1. Windows
Hi

I have an external data source in excel which I need a pivot table on, the data is growing and Im looking for a way of automatically changing (Expanding) the data source.

The data comes from a web query and I am able to get the link from a right click on the data, but I cannot use this link as an external data source for the pivot table so it would appear I need a makro - I can find these to refresh data but not to expand the data selection.

Thanks
 

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
Make source data as Table
From default workbook use PowerQuery to "import" data from source file
Create PivotTable with ExternalData (QueryTable)
---
if source data will be updated, refresh PivotTable and you should see new data in the PT.
it doesn't matter your source file will be open or closed
 
Upvote 0
Data comes from a cloud based finance package and the link to the data source (Right click on data then edit query) is longer than 256 characters so rejected when trying to set a pivot table to run from an external source..I also get a stack of script errors when editing the query..
 
Upvote 0
External source for PivotTable means connection to the local QueryTable on your default workbook not to the cloud

eg.

screenshot-46.png
 
Last edited:
Upvote 0
Tried that, I get 'The type of connection selected cannot be used to create a pivot table'
 
Upvote 0
Not sure myself :) The spreadsheet is created via our finance package and opens as a worksheet with an external data link - that link can only be amended using the finance package but changes made there filter through when the data is refreshed. As such no links are created directly in Excel and I don't think they can be, that's why I think I need to go down a makro route if possible?
 
Upvote 0
did you try : Data - New Query - From Other Sources - From Web then paste link, OK etc....?
 
Upvote 0

Forum statistics

Threads
1,213,527
Messages
6,114,144
Members
448,552
Latest member
WORKINGWITHNOLEADER

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top