98.8% Of all customers recommend us, we're so confident about our results we publish all reviews and stats
View Live Stats View Reviews
Forum home » Delegate support and help forum » Microsoft Excel Training and help » "Cannot find pivot table source" error
"Cannot find pivot table source" error
Resolved · Urgent Priority · Version 2007
Wed 29 May 2013, 09:24Delegate Katy said...
Katy has attended:
Excel Advanced course
PowerPoint Intermediate Advanced course
"Cannot find pivot table source" error
Good morning
I wanted to say thank you so much for all your help so far!
I have emailed a spreadsheet (inc pivot tables and charts) to a colleague. However, when he goes to open the spreadsheet and then refresh the pivot table/chart, the following error message comes up:
Cannot find pivot table source
Is there anything that can be done about this?
Many thanks (again!)
Katy
Wed 29 May 2013, 09:39Trainer Gary said...
RE: "Cannot find pivot table source" error
Hi Katy
Thanks for getting in touch. It's difficult to tell without seeing the file but as it says the first thing to check is where it's drawing the data from.
With your PivotTable selected, go to the Options tab and select Change Data Source. This will tell you where the underlying data for the PivotTable lives. If this comes from an external file that your colleague cannot access or other database, you should check they can get to that data first.
Let me know if there is still an issue after investigating.
Kind regards
Gary Fenn
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
Wed 29 May 2013, 11:04Delegate Katy said...
RE: "Cannot find pivot table source" error
Hi Gary
I'm afraid I can't see an options tab when I select the pivot table. Is there anyway you can send me a screen shot of where this should be?
Thank you
Katy
Wed 29 May 2013, 11:06Trainer Gary said...
RE: "Cannot find pivot table source" error
Hi Katy
I found a screenshot of it here:
http://www.java2s.com/Tutorial/Microsoft-Office-Excel-2007Images/Delete_Pivottable___Click_Pivottable_Report_Click_Optio.PNG
Does this help?
Kind regards
Gary Fenn
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
Wed 29 May 2013, 11:28Delegate Katy said...
RE: "Cannot find pivot table source" error
Hi Gary
Yes that helps, my colleague has found the link.
It says that it is looking up the information from here:
'C:\Users\Katy Cowan\AppData\Local\Microsoft\Windows\Temporary Internet Files\Low\Content.IE5\WIU820JW\[Basildon_Stopped_Work_Data[1].xlsx]Master'!$B$9:$V$53
So it looks like it is trying to look up the information from my master spreadsheet rather than the copy my colleague has saved.
Is there any way round this?
Thank you
Katy
Wed 29 May 2013, 12:34Trainer Gary said...
RE: "Cannot find pivot table source" error
Hi Katy
Looks to me like the PivotTable is linked to a file that was either downloaded from the Internet or from an email.
It is then looking for that file on *your* (Katy's) machine.
Obviously at your colleague's computer it can't 'see' that file as it lives on your computer.
The more robust solution would be to host that master sheet on a shared location, such as a server or network drive. Then replace the Source Data location with this networked version.
I hope this helps.
Kind regards
Gary Fenn
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
Wed 29 May 2013, 13:31Delegate Katy said...
RE: "Cannot find pivot table source" error
Hi Gary
I'm afraid we don't have a shared network or drive in which I can save the original spreadsheet.
Is there anything else that can be done or will the pivot tables have to be created from scratch again?
Thank you
Katy
Wed 29 May 2013, 13:43Trainer Gary said...
RE: "Cannot find pivot table source" error
Hi Katy
If that's the case, you have to store the source data in the same file as the PivotTable.
To recreate this very quickly, arrange your PivotTable so that all data is shown, in other words no filters applied. It doesn't matter what fields you have in the Row and Column areas. In the bottom-right of the table you should have a big grand total of all the data. Double-click this figure and it should generate a sheet containing all the background data.
You should then build a new PivotTable from this data and delete the old one. Now when the file is saved the data will accompany the PivotTable. When data is added / modified on the source sheet, this will be reflected on the PivotTable.
Kind regards
Gary Fenn
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
Wed 5 Jun 2013: Automatically marked as resolved.
Training information:
See also:
|
Welcome. Please choose your application (eg. Excel) and then post your question. Our Microsoft Qualified trainers will then respond within 24 hours (working days). Frequently Asked Questions
Any suggestions, questions or comments? Please post in the Improve the forum thread. |
Excel tip:Use the Ctrl-key for quick navigation in Excel 2010If you want to move quickly to the right, left, top or bottom of your spreadsheet, just press Ctrl and one of the arrow keys. If you want to then select all the data in that particular row or column, hold down the Shift key and press Ctrl and an arrow key. |
