Forum Discussion
Prevent external reference in pivot Data Source
It sounds like you are on the right track with Big_Table being a structured table. Excel tables automatically adjust their references when you add or remove data, and they should ideally avoid the issue of external references. Ensure that all your pivot tables are consistently using the structured table reference.
Hi Jacob. All the pivot tables have always consistently used the structured table reference. Most of the time I have no problem. Sometimes (following some document name or location change) when I open the spreadsheet I get a warning that it contains external references... when I check the pivot source, I see it has changed to an external reference (with Big_Table at the end of the external reference).
Changing the pivot source to a dynamic named range (which references the structured table reference) is a potential workaround which was suggested in another forum. Named ranges follow the spreadsheet around when it gets copied, or the file/location gets renamed, so seem like a good solution. It's just not clear that the solution has worked.