Pivot Table Not Refreshing

[srj1359]

I have a Pivot Table on a "Pricing Sheet" tab. The data comes from the "Master title list" tab. The Master title list has 26 regular columns (A-Z) and 10 columns (AA-AJ) in a table with formulas. The entire range is named "Full List," so the pivot table data source is "FullList."

For some reason, the pivot table is not refreshing properly. If I right click on the data and select refresh, it works, but when I save the document and reopen, the refresh does not apply. I've tried selecting "Refresh data when opening the file" and that does not solve the issue. I've also tried just selecting the entire range of data instead of using the "FullList" name and it does not work. Any ideas?

 

[GraH]

Hi, couple of extra questions.
1. Why is the whole source data not a single table?
2. You say, right click on the data and refresh. Then the data is external, or do you mean right click on the pivot?

That 2nd point is suspicious, that's why I ask. If your data needs to be refreshed separately and before the pivot refresh, simply checking the option in the pivot properties won't do.

 

[srj1359]

1. I don't know. I inherited this tool from others who are more experienced in Excel than I am, so I'm sure there's a reason, I just don't know what it is.

2. My apologies -- I misspoke. I meant right click on the pivot.

 

[GraH]

1. Can't imagine a good reason right away... How does that resize correctly? VBA involved?
2. Okay, mystery... How is the source date expanding? I mean, why is it needed to refresh on open? Are there other events happening as well? Could be the pivot refresh happens before new data is loaded?

The referenced name is it a formula, and are formula calculations set on automatic?

 

[srj1359]

1. They identified the range in "Name Manager" as FullList
='Master title list'!ouptbt_full_us_usd:ModuleTable
The above red text is brought in from a workbook connection to a .csv document that is updated frequently. Could that be why it's not a whole single table?

2. The purpose of this document is to find out what a customer owns and doesn't own, then be able give them a quote or order form for unowned content.

For example: I open the document and in the Pricing sheet tab I click customer 1 from a slicer. The Master title list tab show me this customer does own products 1-10, but the pivot table in the Pricing sheet tab is not updating to show this.

I hope that makes sense, but please let me know if I need to clarify anything. I really appreciate your willingness to help!

 

[GraH]

1. As I thought the data source is external. One can write formulas next to the imported range that resizes to the new data range. I'm assuming this is going on.
2. I do suspect this range is refreshed on open as well. It may well be the refresh of the pivot happens before the data query refresh and so you think the pivot is not updated: it does not contain the new data,. but it is refreshed. The same happens when in earlier versions you have a Power Query that generates a table which is then used in a pivot. The refresh of the pivot has priority (personal observation).

I see an alternative to have the data from the CSV imported via PQ and have, if possible, the formulas in custom columns. Finally load the data directly into the pivot. That way there is only 1 refresh required.
Other way around is having a small macro recorded in which you do the refreshes one by one, and run this macro "on open".

 

[srj1359]

Sometimes when I go to refresh the data I get the error message "Excel could not get the source data for the PivotTable report from the disk." Maybe this is connected to the issue? Any thoughts on how to fix that error?

 

[GraH]

That is strange because the pivot's source data is a named range that refers to a range in the same file (right?). Thus this named range include the full path to itself?

 

[sandy666]

error message "Excel could not get the source data for the PivotTable report from the disk."

just out of curiosity, did you save this file?

 

[srj1359]

Yes, I did.