How to set data source for pivot table permenant even if file name or path changed in case data source is already within the same workbook

[MGadAllah]

Hi,
The pivot table I am making contains data source that is already existing in the same file.
As soon as file name changed or file path location changed , the pivot table no longer working and as soon as I do any refresh in the pivot able it is reporting a error that data source does not exist.
I've tried several ways as set data source as a table but no way.
This is the file:-
https://www.dropbox.com/s/c4lfagmxzae0meo/Total_Employee_Performance.xlsx
So how can I make or set data source to be permanent so in case file renamed or copied from place to another ... pivot table still works.
Thanks

 

[MGadAllah]

Hi,
I am sorry for being late replying your question.
The solution for my problem was to format the data I've as a table, then use this table as a data source for the pivot table.
This way even if you moved the file from place to another it is still using the same data source.
Try it and feed me back if you still have any problem.
Format data as table 1st before creating the pivot table in order to guarantee keep data source the same in all condition.

 

[tfmeier]

Silly question. How do you format the data as a table? I have exactly the same problem and always have to go to change data source and then remove the file name contained in [.....] after I have renamed the file name

Hi,
I am sorry for being late replying your question.
The solution for my problem was to format the data I've as a table, then use this table as a data source for the pivot table.
This way even if you moved the file from place to another it is still using the same data source.
Try it and feed me back if you still have any problem.
Format data as table 1st before creating the pivot table in order to guarantee keep data source the same in all condition.

 

[MGadAllah]

Silly question. How do you format the data as a table? I have exactly the same problem and always have to go to change data source and then remove the file name contained in [.....] after I have renamed the file name

Like this video here:
https://www.youtube.com/watch?v=zD7LPePnDfM
Just select the entire data then format it as a table as shown in the video, then select any cell in the table, then select summarize with pivot table, then configure the pivot table the way you want.
Now when you click on change data source you will find it says the name of the table instead of full path.

 

[tfmeier]

Great. Thank you. Will try that later today. Hopefully I don't have to re-create all the pivots but will let you know how it goes.

Thanks again!

Like this video here:
https://www.youtube.com/watch?v=zD7LPePnDfM
Just select the entire data then format it as a table as shown in the video, then select any cell in the table, then select summarize with pivot table, then configure the pivot table the way you want.
Now when you click on change data source you will find it says the name of the table instead of full path.

 

[MGadAllah]

Great. Thank you. Will try that later today. Hopefully I don't have to re-create all the pivots but will let you know how it goes.

Thanks again!

Wish you good luck.

 

[tfmeier]

It worked. Had to designate the data as table as indicated in the video followed by recreating the pivot tables but now everything works fine.

Thank you again!

Wish you good luck.

 

[MGadAllah]

I am glad it did because I was suffering to fix such a thing

 

[BOYAR]

Hi everyone, because i have very similar question i have just replied here instead of creating new one.

In my case i have 2 pivots which Table/Range refers to a full file name and then to the Table name, like that : 'File name.xlsm'!Table2

Thing is that very often file name is beeing changed and then pivot stop working due to this. I know that i can remove file name and leave just a Table2 reference, but then after each time file is saved, its name is beeing inserted again in to Table/Range field of Pivot.

Is there a way to stop that and force the pivot to refer only to the Table2 ?

Thanks

 

[BOYAR]

Ok i found a solution. I had to changed reference Range/Table from Table2 in to exact area where the data is, for example

'Hardware Data Base'!$A$5:$O$800 and then it worked each time when file name was changed. For some reason static reference to the Table2 was adding file name each time when file was saved.