Excel Pivot Table Refresh problem - when data is changed causes ?NAME error in table

HI Guys

Hope you can help

Ive got a spreadsheet which is linked to a pivot table for some monthly supply chain data

Its been working OK but this month Ive updated it for August but needed to change some cost values - when I did this and refreshed the pivot it goes blank with ?NAME in all the cells?

Ive tried to see if something is wrong but cannot find any issue except for the fact that the 1st August date in the Caluculated field list comes out as 1/08/20172 but in the list it is correct?

I have attached the file - the tab is Monthly Summary

If you could help I would be very grateful

Jase AlexanderCompliance ManagerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rob HensonFinance AnalystCommented:
I have been able to fix it by doing the following.

Go to Data sheet and in cell FD2 press F2 and enter just to re-confirm the date entry.

Refresh the pivot and the values go to Name? as you're seeing.

In your Pivot go to the Calculated Fields dialogue and scroll down to the Aug Total and the formula will now have 'Name?' instead of the 01/08/2018 field, delete and re-insert the 01/08/2018 field. Click OK and then refresh the Pivot again.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Jase AlexanderCompliance ManagerAuthor Commented:
Thank you Rob - works perfect !
Rob HensonFinance AnalystCommented:
Glad to help.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.