Pivot table data ranges don't update with workbook name changes

[Corrado_]

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!

Thanks!

[dflak]

How are you assigning this dynamic range? I've experimented with changing the source data manually and it works almost as advertised. I changed the workbook name, but not the range of cells on which the pivot table is built, so the new data was limited to the original range of cells.

I recorded a macro and it looks like you would have to detect and change the number of rows in the new source data.

[Corrado_]

Hi dflak,

I set up a dynamic range called 'pivot data' in name manager with this formula: =OFFSET(data!$A$1,0,0,COUNTA(data!$A:$A),COUNTA(data!$1:$1))

Then just put 'pivot data' as the pivot table source. Works perfectly until the workbook name is changed.

Could you please elaborate on your system a bit? Could be a good alternative.

Thanks!

[dflak]

I did not know how flexible I had to make this, so the control panel has a bunch of named ranges used in the code. Of particular interest is cell B2. It seems that the differences in the source file name is limited to the date stamp. So you could probably use a formula to calculate the source file name based on TODAY().

Although most items are defined on the control panel, there are still a couple of items that need to be "configured." I called my pivot table PT_Test. You will have to name yours and change it on the control panel.

The source data I used only has two columns. You will have to change the code to reflect the number of columns your data has. The "C2" in the following means column 2. If your data goes to column E then use "C5."

Please Login or Register  to view this content.

An additional assumption is that your source data starts on column A.

[Corrado_]

Hi dflak,

Thank you for your response. Unfortunately, I can't get your solution to work. Would it be possible to adapt your code to simply change the pivot source to "pivot data" or whatever specified text? it will be the same every time and referring to file paths seems overly complicated.

Thanks again.

[dflak]

I made it as flexible as possible since I had no idea how things were set up. If you can attach a sample workbook with the pivot table, and a sample workbook with the source data, I can eliminate a lot of the things you have to configure by hard coding them. I can also make the assumption that the source data is in the same directory with the workbook containing the pivot table wherever that might be.

Use sample data that is not sensitive or proprietary.

To attach a workbook: Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.