Pivot Table Data Source

wildturkey

Board Regular
Joined
Feb 21, 2006
Messages
156
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
 

Some videos you may like

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.

sandy666

Well-known Member
Joined
Oct 24, 2015
Messages
5,448
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
 

wildturkey

Board Regular
Joined
Feb 21, 2006
Messages
156
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..
 

sandy666

Well-known Member
Joined
Oct 24, 2015
Messages
5,448
External source for PivotTable means connection to the local QueryTable on your default workbook not to the cloud

eg.

 
Last edited:

wildturkey

Board Regular
Joined
Feb 21, 2006
Messages
156
Tried that, I get 'The type of connection selected cannot be used to create a pivot table'
 

sandy666

Well-known Member
Joined
Oct 24, 2015
Messages
5,448
could you post what are you doing from the beginning? step by step?
 

wildturkey

Board Regular
Joined
Feb 21, 2006
Messages
156
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?
 

sandy666

Well-known Member
Joined
Oct 24, 2015
Messages
5,448
did you try : Data - New Query - From Other Sources - From Web then paste link, OK etc....?
 

sandy666

Well-known Member
Joined
Oct 24, 2015
Messages
5,448
other way...
Can you save this file (opened from the link) locally?
 

Watch MrExcel Video

Forum statistics

Threads
1,099,889
Messages
5,471,317
Members
406,755
Latest member
CalJake

This Week's Hot Topics

Top