Pivot table data source not staying within the file

%3CLINGO-SUB%20id%3D%22lingo-sub-1247378%22%20slang%3D%22en-US%22%3EPivot%20table%20data%20source%20not%20staying%20within%20the%20file%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1247378%22%20slang%3D%22en-US%22%3E%3CP%3EHi%3C%2FP%3E%3CP%3EI've%20got%20a%20lot%20of%20pivot%20tables%20working%20of%20a%20large%20dataset%20all%20in%20one%20excel%20file.%26nbsp%3B%20Only%20issue%20is%20that%20as%20I%20save%20the%20file%20on%20diff%20dates%20I%20want%20version%20control%20but%20the%20pivot%20tables%20then%20keep%20looking%20at%20the%20original%20data%20source%20file%20when%20it%20was%20first%20created%20they%20don't%20just%20keep%20referring%20to%20the%20tab%20within%20the%20file.%26nbsp%3B%20I'm%20not%20changing%20tab%20names%20or%20anything%20else%20just%20the%20file%20name.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIs%20there%20an%20easy%20was%20to%20update%20the%20pivot%20tables%20so%20it%20stops%20looking%20externally%20for%20the%20data%20source%3F%26nbsp%3B%20I%20don't%20want%20to%20apply%20a%20macros%20but%20this%20is%20really%20annoying%20given%20the%20number%20of%20pivot%20tables%20in%20the%20file%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1247378%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1247726%22%20slang%3D%22en-US%22%3ERe%3A%20Pivot%20table%20data%20source%20not%20staying%20within%20the%20file%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1247726%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F591504%22%20target%3D%22_blank%22%3E%40EMcil82%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20you%20have%20the%20Data%20tool%20bar%20open%2C%20you%20should%2C%20with%20your%20screen%20open%20to%20the%20Pivot%20Table%2C%20be%20able%20to%20click%20on%20%22Refresh%20All%22%20to%20have%20it%20update%20the%20data%20on%20which%20the%20Pivot%20Table%20is%20based.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1249207%22%20slang%3D%22en-US%22%3ERe%3A%20Pivot%20table%20data%20source%20not%20staying%20within%20the%20file%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1249207%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F425987%22%20target%3D%22_blank%22%3E%40mathetes%3C%2FA%3Ebut%20that's%20the%20problem.%26nbsp%3B%20If%20you%20save%20the%20file%20after%20doing%20work%20and%20put%20a%20new%20description%20on%20the%20file%20the%20pivot%20table%20then%20looks%20to%20the%20old%20file%20for%20the%20dataset%20it%20doesn't%20keep%20the%20connection%20within%20the%20current%20file%20being%20worked%20on.%26nbsp%3B%20It's%20crazy%20that%20every%20time%20I%20do%20a%20'save%20as'%20and%20rename%20the%20file%20I%20then%20have%20to%20go%20and%20change%20all%20the%20data%20sources%20of%20the%20pivot%20tables%20to%20look%20at%20the%20new%20file%20and%20not%20the%20previously%20named%20file.%26nbsp%3B%20make%20sense%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1249240%22%20slang%3D%22en-US%22%3ERe%3A%20Pivot%20table%20data%20source%20not%20staying%20within%20the%20file%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1249240%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F591504%22%20target%3D%22_blank%22%3E%40EMcil82%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMaybe%20this%20link%20is%20useful...%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fsocial.technet.microsoft.com%2FForums%2Fen-US%2F582af2b2-447d-4f38-9f76-b3f5e919cd86%2Ffile-save-as-changes-pivot-table-data-source-to-absolute-address%3Fforum%3Dexcel%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fsocial.technet.microsoft.com%2FForums%2Fen-US%2F582af2b2-447d-4f38-9f76-b3f5e919cd86%2Ffile-save-as-changes-pivot-table-data-source-to-absolute-address%3Fforum%3Dexcel%3C%2FA%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1249341%22%20slang%3D%22en-US%22%3ERe%3A%20Pivot%20table%20data%20source%20not%20staying%20within%20the%20file%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1249341%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F591504%22%20target%3D%22_blank%22%3E%40EMcil82%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIt%20does%20make%20sense.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAnd%20after%20following%20the%20thread%20that%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F591504%22%20target%3D%22_blank%22%3E%40EMcil82%3C%2FA%3E%20gave%2C%20I%20see%20it's%20not%20a%20new%20problem%2C%20nor%20one%20that's%20easily%20resolved.%20I%20had%20missed%20in%20the%20first%20instance%20that%20you%20were%20getting%20data%20from%20a%20different%20spreadsheet%20(different%20file).%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI've%20had%20similar%20issues%20with%20a%20workbook%20I%20created%20that%20pulls%20data%20for%20financial%20analysis%20from%20a%20daily%20download%20from%20a%20brokerage....there've%20been%20occasions%20when%20I've%20moved%20from%20one%20computer%20to%20another%20with%20those%20files%20in%20'the%20cloud%22%20and%20then%20gotten%20all%20kinds%20of%20error%20conditions%20when%20I'm%20expecting%20my%20main%20analytical%20sheet%20to%20link%20with%20the%20fresh%20downloads.%20Frustrating%2C%20to%20be%20sure.%20Even%20when%20it%20looks%20to%20me%20as%20if%20all%20files%20are%20within%20the%20same%20folder%2C%20on%20the%20same%20desktop....%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESo%20I%20feel%20your%20pain%2C%20but%20have%20no%20ready%20solution%20either.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
New Contributor

Hi

I've got a lot of pivot tables working of a large dataset all in one excel file.  Only issue is that as I save the file on diff dates I want version control but the pivot tables then keep looking at the original data source file when it was first created they don't just keep referring to the tab within the file.  I'm not changing tab names or anything else just the file name.

 

Is there an easy was to update the pivot tables so it stops looking externally for the data source?  I don't want to apply a macros but this is really annoying given the number of pivot tables in the file

 

4 Replies
Highlighted

@EMcil82 

 

If you have the Data tool bar open, you should, with your screen open to the Pivot Table, be able to click on "Refresh All" to have it update the data on which the Pivot Table is based.

Highlighted

@mathetesbut that's the problem.  If you save the file after doing work and put a new description on the file the pivot table then looks to the old file for the dataset it doesn't keep the connection within the current file being worked on.  It's crazy that every time I do a 'save as' and rename the file I then have to go and change all the data sources of the pivot tables to look at the new file and not the previously named file.  make sense?

Highlighted

@EMcil82 

 

It does make sense.

 

And after following the thread that @EMcil82 gave, I see it's not a new problem, nor one that's easily resolved. I had missed in the first instance that you were getting data from a different spreadsheet (different file).

 

I've had similar issues with a workbook I created that pulls data for financial analysis from a daily download from a brokerage....there've been occasions when I've moved from one computer to another with those files in 'the cloud" and then gotten all kinds of error conditions when I'm expecting my main analytical sheet to link with the fresh downloads. Frustrating, to be sure. Even when it looks to me as if all files are within the same folder, on the same desktop.... 

 

So I feel your pain, but have no ready solution either.