I have an excel workbook with various pivot tables, all of which reference a TABLE (named Watts) that is located WITHIN the same workbook. There are no links to external data anywhere within the workbook. I have created this as a template for myself () so that each month I can do a "Save as" and rename the file with the current year/month and save in a new/different folder (current month folder) then where the template resides. I have used this method for YEARS without fail. However, for the first time ever, I am now seeing a few of my templates Misbehaving.
Because I ensure the named table(s) are within the workbook (and NOT an external source/connection), what should happen is that the pivot tables continue to reference the table "Watts" within the workbook -- regardless of what the filename is. However, what is happening, is that when I open the renamed file, the pivot table source immediately references the old filepath&table, rather than simply referencing "Watts" (the table WITHIN the current file). The old file name is embedded in pivot table data reference, and should not be.
After renaming the file to the new month, the pivot table reference should be unaffected and continue to be: "Watts" (within the file). Instead, the pivot table source data reference becomes the template file path & Watts. I have never seen this happen before and must clearly be a bug. I was having this issue on Excel 2013 and so, just to be safe, I had my IT department upgrade me to Excel 2016. However, the same issue persisted.
P.S. I just did some more testing and realized that it actually isn't even happening at the 'rename' phase. It's happening right within the template file. I will click Pivot > Change Source Data > and in the range I will type Watts to direct it to my table. The source is accepted without issue and the pivot table updates with no problems. I then click on Pivot > Change Source Data to take a look at it again, and I notice that the source data has changed to include the filepath in it! So, I'm not sure where/how this is happening.
I would upload screenshots but I don't have permission to upload images.
