Forum Discussion
Prevent external reference in pivot Data Source
Hi, I'm trying to prevent external references being created in new copies of an existing spreadsheet. The issue seems to be limited to the Data Source for pivot tables.
My spreadsheet includes various pivot tables based on one big table. That big table is the main thing that gets updated in the spreadsheet. The pivots use the table name (let's call it Big_Table) as the data source, so that the pivots can more easily adjust to changes to the table (additional rows etc).
I need to ensure the pivots are always referencing the version of Big_Table that's in the same workbook as them, but I keep running into an issue where - following some kind of change in filename or location - the pivot Data Source changes to an eternal reference (i.e. referencing an old version of Big_Table in a previous version of the spreadsheet).
I've tried unticking 'Save source date with file' and ticking 'Refresh data when opening the file' in Pivot Table Options, and a few other things, and I keep thinking I've solved it, then another external reference appears. I'm having difficulty consistently recreating the issue.
My latest attempt involves creating a dynamic named range (Range_Big_Table) which refers to Big_Table, then changing the Data Source for each pivot to the dynamic named range. Feels like this should lock the pivots into using the 'local' Big_Table, and I haven't managed to recreate the issue since, but I'm not confident... particularly as when I click Change Data Source (to check the data source for each pivot), it's reverted back to the table name instead of the range name.
Can anyone help/give me some sanity?
Some maybe relevant extra info:
- The big table really needs to be formatted as a table, not as a range.
- The file is generally stored in SharePoint. It may get worked on by more than one person.
- The file needs to work in 'standard' MS365 Excel without needing Add-ins.
- The file may get renamed, saved-as and renamed, downloaded from SharepPoint, uploaded or moved to a different SharePoint folder, and the SharePoint folder may also get renamed.
- I know I can fix this by manually amending the pivot Data Source for each table. That's not scalable or a viable solution for other users of the spreadsheet.
- I believe I can fix this by moving all pivots to the same worksheet as Big_Table, but that's not viable here.
- It's a macro-enabled workbook. I've linked a (recorded) 'refresh all pivot tables' macro to a refresh button next to each pivot which seems to work fine. I'd love to replace this in the future with some VBA automation, but p[roper VBA (not just recording a macro) is extra complexity I'm trying to avoid for now if possible.
- I've messed around with Power Query but it wasn't as flexible as pivots, and caused security warnings, so it's a no-go here.
All help appreciated!
[Update]
After more digging, I think the issue relates to the pivot table cache (rather than the pivot table) staying linked to the old version of the workbook, as described at https://www.encyclopedia-excel.com/how-to-update-pivot-table-data-sources-on-new-copied-workbooks-with-vba .
I also think I MIGHT be able to solve the issue without vba, by setting Number of items to retain per field box to None , as described at https://support.microsoft.com/en-us/office/items-that-may-have-cached-data-found-27cd3af4-efd7-4bb4-bee8-46315a2f83cc .
Can anyone advise/confirm?
Since you mentioned that your pivot tables are based on a table named Big_Table, one of the most effective ways to keep pivot tables updated without external references is to ensure that the source table (the "Big_Table") is consistently recognized by Exce.
Hi can you explain what you mean by 'ensure that the source table (the "Big_Table") is consistently recognized by Exce.'?
Since you mentioned that switching to a dynamic named range leads to issues where pivot table data sources revert back to the table name, consider avoiding named ranges altogether. Rely solely on the structured table references (like Big_Table) for the pivot tables.
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.
