pivot table missing field

TinaP

Well-known Member
Joined
Jan 26, 2005
Messages
528
Please accept my apology in advance, this is going to be a long post.

I have a large workbook which gets sent to our board of directors every month. The data is downloaded from our mainframe and compiled in a useful form by a macro. It has worked well for the past seven years, with occasional tweaks necessitated by examiners or management requests. Among other things, there is a pivot table on one sheet generated from data on another sheet. Last month, management asked me to add another field to a data sheet and, subsequently, the pivot table. I added the field, adjusted the macro, and everything worked great—until I tried to balance it. After adding the column, the new field will not show in the Field List. The new field was added in the middle of the data and the fields to the left and right show just fine.

What I’ve tried:

  • I have refreshed the pivot cache repeatedly.
  • The named range which I’m using as the pivot table data source selects all of the data.
  • I have deleted and re-created the pivot table.
  • I have deleted and re-created the pivot table worksheet and the data sheet.
  • I have been researched this online for the past two days. I found a tool/macro at http://www.contextures.com/xlPivot11.html that leads me to believe the data from this past month isn't being absorbed in the pivot cache, but I can't figure out how to get it to work.

Other useful info:

  • I’m using Windows 7 Professional, Excel 2010.
  • The additional field shows on another computer using Windows 7 Professional, Excel 2016. Swapping computers is not an option.

What am I missing? Management is not aware of the problem and I’d like to fix it before they find out. :rolleyes:
 

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.
first I would check is the columns are actually sequential and visible

and I would also change the data source in the toolbar when the pivot is selected. I suspect what you have done has already covered that off
 
Upvote 0
Wow! Thank you for responding so quickly.

The columns are in order and visible. I've repeatedly changed the data source in the frantic hope that "This time it will work" but it never does.
 
Upvote 0
Success of a sort!

You gave me an idea to use the normal row and column references rather than a dynamic named range for the data source and it updated properly. WooHoo!! Now I need to figure out how to have the range update automatically since the data grows monthly.

It's funny, though. The data is in 'New Comm'!$A$1:$N$753, when I enter ? Range("NC_PivotRange").Address in the Immediate window of the VB editor, it says $A$1:$N$753. Everything points to it working correctly, but not so much.
 
Upvote 0
I changed the data range to a table and it's working fine now.

Thank you for your time and help.
 
Upvote 0

Forum statistics

Threads
1,213,513
Messages
6,114,072
Members
448,546
Latest member
KH Consulting

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top