Pivot table source path

I have a pivot table in one workbook whose source data is in another workbook. If I move the source file the second file gives me an error message because it can't find the pivot table. I'm glad that appeared now. I will be distributing this setup to another user who will do the same thing I do for the other half of the company.

I'd like to make it so the pivot table will update as long as the source workbook is in the same folder as the destination. That way I can put THEM BOTH on my desktop while my friend puts them in a folder off the C drive if he wants to.

Is this possible? If not I will have to merge the two workbooks. Due to the sheer number of formulas and macros each book has that might create a few problems on it's own.

I appreciate any discussion. I don't expect anyone to give me a turnkey answer. Whatever you advise that I don't understand will go on my reading list.

many thanks to all.

Hi Dave. I don't understand your comment sir. I have two separate workbooks. One is for techs and the other is for inventory control. In one tab of the tech workbook there is a pivot table. That pivot table is made with data from a range in the inventory control workbook.

I moved the inventory control workbook from my desktop to my Google Drive folder. When I went to refresh the pivot table in the tech workbook, it told me it couldn’t find the source data. I can change the data source by adding my google drive location and it all works again.

I’m trying to figure out a method so that as long as the two files are in the same folder, any folder, they will still work together. Thanks. For taking the time to look at my question.

Wow that is harder than I thought it would be. I can't find anything in the VBA Editor that defines the pivot table. In despair I even tried pasting this into the "change source data" part on the ribbon. NOT gonna work. Bummer.

Please Login or Register  to view this content.