How to set data source for pivot table permenant even if file name or path changed in

MGadAllah

New member
Joined
Jan 11, 2014
Messages
22
Reaction score
0
Points
0
Excel Version(s)
2016 and 2019
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
 

p45cal

Super Moderator
Staff member
Joined
Dec 16, 2012
Messages
2,168
Reaction score
10
Points
38
Excel Version(s)
365
In the Change PivotTable Data Source dialogue box you currently have:
'\Users\MohamedGad\Dropbox\ahmed-survey\New folder\employee_performance.xlsx'!Table1
change it to:
Table1
 

MGadAllah

New member
Joined
Jan 11, 2014
Messages
22
Reaction score
0
Points
0
Excel Version(s)
2016 and 2019
In the Change PivotTable Data Source dialogue box you currently have:
'\Users\MohamedGad\Dropbox\ahmed-survey\New folder\employee_performance.xlsx'!Table1
change it to:
Table1
each time I change it to Table1, as soon as I move it to another place it is break once again.
 

p45cal

Super Moderator
Staff member
Joined
Dec 16, 2012
Messages
2,168
Reaction score
10
Points
38
Excel Version(s)
365
I've just tried to replicate what's happening with you and I can't.
You are saving the file after you've reduced the source to just Table1 but before you try to copy/move it?
 

MGadAllah

New member
Joined
Jan 11, 2014
Messages
22
Reaction score
0
Points
0
Excel Version(s)
2016 and 2019
I've just tried to replicate what's happening with you and I can't.
You are saving the file after you've reduced the source to just Table1 but before you try to copy/move it?

what do you mean?
 

p45cal

Super Moderator
Staff member
Joined
Dec 16, 2012
Messages
2,168
Reaction score
10
Points
38
Excel Version(s)
365
what do you mean?
Well,
I've just tried to replicate what's happening with you and I can't.
This means that I've taken your file, saved it on my drive, changed the source data reference to Table1, saved the file, closed it. Then
1. I've takes a copy of the file and saved it somewhere else, and
2. I've also moved the file somewhere else
In both cases the data source reference remained Table1, which is the behaviour I expected.
With you, you say it does something else; I wasn't able to replicate the behaviour you get over here (replicate = get the same behaviour here).





You are saving the file after you've reduced the source to just Table1 but before you try to copy/move it?
I'm checking here that you have saved the file after you've shortened the source data reference to Table1, and before you try moving it around or copying it somewhere else. If you don't save the file you won't change anything.

Now do you understand what I mean?
 

MGadAllah

New member
Joined
Jan 11, 2014
Messages
22
Reaction score
0
Points
0
Excel Version(s)
2016 and 2019
Thanks a lot and your time :)
 

p45cal

Super Moderator
Staff member
Joined
Dec 16, 2012
Messages
2,168
Reaction score
10
Points
38
Excel Version(s)
365
So, is your problem solved?
 

MGadAllah

New member
Joined
Jan 11, 2014
Messages
22
Reaction score
0
Points
0
Excel Version(s)
2016 and 2019
So, is your problem solved?
Yes I rebuild the whole file and did not copied anything from outside the file and set the data source as a table and it is working now.
 
Top