On copying pivot table and changing datasource some calculated formulas disappear

[Stepjack]

I have 10 datatables on 10 tabs in a workbook, each tab named 1 to 10. Each pulls in the same data types by column, the data in the rows being diffferent. Each datatable will pull in various number of rows of data. As such I created named ranges (data1 for tab 1 data; etc.) and use an offset formula to ensure all data is grabbed.

There are many calculations that I have created in the pivot table to reduce file size when pulling data (e.g. Avg Price = Dollars / Units).

I have just made some updates to my formulas in the first pivot table on tab 1A associated to datatable data1 on tab 1. I now want to copy the pivot table with the formulas accross the other tabs 2A to 10A in order to have all the newly created formulas resident when I change datasources to the correct tables.

However, when I do this the formulas do not copy. I have tried selecting "Entire PivotTable" and copy-pasting, and I have tried copying the entire tab by ctrl-drag(?) and then changing datasource to data2 (e.g.). Neither method works.

I have to believe there is something I'm missing on how replicate a pivot table and change the datasource without all the (new) calculations disappearing.

Any help is appreciated. thanks,

 

[Jerry Sullivan]

Hi and Welcome to MrExcel,

Each pulls in the same data types by column, the data in the rows being different.

Are the field names in the source data tables identical? What you describe should work if the names referenced by the calculated field formulas match, it won't if they are different.

 

[Stepjack]

Hello and thank you for the quick reply. Unfortunately, the fields are exactly the same. The data pulls come from a syndicated data source and once I have my fields selected, I simply copy the report to a new tab and change the product group I want to report on. Just in case something strange was happening I copied the field headers from the reports and used a formula to ensure they matched. The odd part is that after I delete a pivot table and replace it with a new one, and I change the data source, the calculations that remain are those that were in the previous pivot table associated to this data source. There are two other pivot tables associated to each data pull in addition to the one I'm trying to replace. Could these be somehow taking precedence on the data source and limiting any new calculations on new sheets/pivot tables associated with the same source?

 

[Jerry Sullivan]

Let's focus on one of the scenarios you described in your Original Post....

You select the entire PivotTable on Sheet 1A > Copy > Paste into Sheet 2A > change the data source from data1 to data2.

At what point do your calculated fields that existed in the pivot on Sheet 1A disappear from the pasted pivot on Sheet 2A?

Do the other fields that are displayed (using source fields), correctly update to use the values in data2 instead of data1?

 

[Stepjack]

The calculated fields disappear the moment I hit 'refresh'

 

[Stepjack]

The other fields correctly update.

 

[Stepjack]

To clarify; the calculations disappear as soon as I click OK after changing from data1 to data2.

I have tried other named ranges (data 5, data10, etc.) with same result. Changing the range back to data1, does not bring back missing calculations. It leaves the reduced set.

Interestingly, I just tab-dragged a new copy, clicked 'change data source' and instead of selecting a new named range, I clicked into the range selector, cicked on tab 2 (data tab) and accepted the default range that was in the dialogue box. This range A1:AL661, matches the range on data tab 1 that would be defined by the named range data1 (as defined by the offset formula). This does not cause my new formulas to disappear. If I then try to change the range back to the named range, the formulas again disappear.

I tested this on a tab with data that extends to 1100+ rows. After accepting the default (661 rows), I went back in and selected all additional rows of data, clicked OK and the calculations remain.

However, because the number of rows in each data tab can change each month, it is optimal to use the offset function to capture.
Maybe the OFFSET formula just doesn't work this way?

Is there a better formula to use for my named ranges than: =OFFSET('1'!$A$1,0,0,COUNTA('1'!$A:$A),COUNTA('1'!$1:$1))

thanks

 

[Jerry Sullivan]

When I mockup a workbook using your formula for the named ranges, I'm able to copy the PivotTable from sheet 1 to 2 then change the datasource from data1 to data2 without losing the calculated field "Price".

I typically use the non-volatile INDEX function instead of OFFSET for Dynamic Named Ranges; however since I'm able to retain the calculated field while using your OFFSET formula for the datasource ranges, that doesn't appear to be a factor.

What version of Excel are you using?

Are you able to share an example workbook (with any sensitive data removed/changed) that exhibits that behavior?
You could either upload to a hosting site like Box.com or send a PM and we'll exchange email addresses.

 

[Jerry Sullivan]

Thanks for sending me an example file by email. Unfortunately, I wasn't able to replicate the problem.

I tested copying the PivotTable two ways: Copying the entire Sheet 1A and Copying the PivotTable's Range then pasting it in another sheet.

In both cases, I was able to change the data source of the new copy of the Pivot from
'1'!$A:$AL (what you had in the file you sent) to named range "data1" to "data2".

After each change in data source, I refreshed the Pivot and it retained its Calculated Fields.

If you aren't able to do the same, perhaps there's a difference in that behavior for your version of Excel.
Which version are you using?

 

[Stepjack]

Thanks for checking. Although that is a bit furstrating that you can't replicate it.

I'm using 2010 version 14.0.7140.5002 32bit. I just tried it again, and my formulas in columns AF:AN still disappear when I change the selection to the named range to data1. More interestingly, when I change to data3 or 4, an additional 3 columns disappear! (AC:AE)...very strange.