• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Fix reference to table

P

Pierre

Member
Hello,

I have a chart and some pivot table that are linked to a table (Table1). Everyday I update the data in the table, refresh the chart and pivot table and save as today.xls

Everything was ok until yesterday: when I saved my file under the new name it was not possible to update my chart and pivot table, as now the source was refered as "'yesterday.xlsx'!Table1"

Is there a way to fix the reference of the source so that it doesn't change even when I change the name of the file?

Thanks
 
Changing the name of an Excel file should have no impact on the work book, so some thing else is going on, just where are you change the file details, and are you actually changing it to "yesterday"?


.
 
I had no problem for a long time, it just from yesterday that the problem started. What I do is: open file/add new date in table/save file name as "today"/ refresh the chart. And when refreshing the pivot table I have the error message "cannot open pivot table source file 'yesterday.xls'"

maybe something happened when I installed updates of Windows (I have windows 7, excel 2013). Now I looking for a solution to lock the reference to the table...
 
Open the file
Goto Data
Edit links
Select the incorrect Link and change it
 
The "Edit Links" is not active....

My main question is why excel add "filename.xls!" to the reference of the table: before it was only "Table1", so no issue when I was saving my file under a new name. Now it is always "filename.xls!Table1", which cause problem when I save with new file name and want to refresh the pivot table...
 
Strange

You can simply Right click on the Pivot table
Change Data Source
and change 'Daily report_27.01.15.xlsx'!Table1
to Table1

I'm not sure why the behaviour has changed?

Have you opened the file in different versions of Excel to what you normally do ?
 
Hello Hui,

That's what I did, but when I save the save under a new name, the reference 'Daily report_27.01.15.xlsx'!Table1 comes back

I'm using the same excel version, nothing changed in last few days, except the fact that I installed windows 7 updates.

I have no idea why it does that and thought maybe there is a way to fix the reference address whithout having the file name in it, or some options has something to do...?
 
Hi Pierre ,

Can you check this file , and see whether the problem exists in this too ?

Narayan
 

Attachments

  • Daily report_27.01.15_version1.xlsx
    102.8 KB · Views: 3
Hi Pierre ,

I downloaded the file from my post , and checked ; I see only Table1 and not the table name prefixed with the file name.

Narayan
 
Then I guess it's some internal settings of excel which were changed somehow... So I will keep a name that will not change for updating my file and add new data, and only save as daily name after the chart and pivot are updated. Thanks everyone for your help and time!
 
You must log in or register to reply here.
Back
Top