I have a dynamic name range, "pivot data", which I use as the data source for a pivot table.
When I change the name of the workbook the pivot table refers to the name range in the old workbook.This is for a report so the name is changed regularly.
E.g if I change the name from "report 24/11/16" to "report 25/11/16", the pivot will refer to "'report 24/11/16.xlsx'!pivot data" instead of just "pivot data" (or "'report 25/11/16.xlsx'!pivot data" )
Does anyone know a way around this? Having to change the data source each time defeats the purpose of having dynamic name ranges for pivots!