Source of Pivot Table

[NishchayMunshi]

Hello Everyone,

I am a Planning Engineer at an EPC project Site and I use MS Excel to monitor the Project Progress.

I have recently learned the Pivot Table option to summarize the progress data.

Since I have to update on a daily basis and keep a record of daily progress, I create a copy of previous day's file and update the data. But when I go on to refresh the Pivot table, it gives an error, Cannot open PivotTable Source File "-------". In the reference it shows the name of previous day's file.

This is unique, because I have been updating progress in similar way for other packages and this error does not come.

I also tried to attach the excel file with this thread, but the attachment option does not seem to work.

[TMS]

Yes, the attachment icon doesn't work these days, for some reason, so ...

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window (or use the paperclip icon).

[TMS]

When a cell in the Pivot Table is selected, click on the Options Ribbon and try using "Change Data Source"

[NishchayMunshi]

The 'Change Data Source' Worked. Thank you very much.
But it still does not solve the problem, as I have to do it every time I update the copy of the file.

I have attached 2 sample files of my work. I have highlighted the changes in 'Orange'.

In the First Sheet, I create a new pivot table and the changes in the source reflect in the table.
But in the next day's update, as I refresh, the pivot table refers the previous day's sheet as source and does not update the pivot table.

[Cdubisms]

If it were me, I'd make the source data a Excel table because tables are dynamic and then build a pivot table off of that Excel table.

I've attached an example.

Let me know what you think.

[NishchayMunshi]

This is exactly what I have done. I created a excel table and built a Pivot table out of it.

The problem of making an update in the next copy of the file is still there.

Please check the attachment. As I create a copy of the file for next day's update and refresh the Pivot table, it gives the same error.

But I can change the Source every time I create new copy of the previous day's file. This is working for me.

Thank you everyone for your time and help. I truly appreciate it.