Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 125
  • Last Modified:

Pivot table created

Folks,
In the attached workbook there is a lesson from the "Menu" tab labeled "Combo Box with Macro" There are some instructions on the right side that you're welcome to read. My problem has to deal with the data used in creating the pivot table. Click to "View Raw Data". When you do that the worksheet "Raw Data -2"  appears. When you display the pivot table fields you'll se a field labeled "Years".  I have no idea where that field "Years" came from.
Interactive-Controls.xlsm
0
Frank Freese
Asked:
Frank Freese
  • 3
  • 2
1 Solution
 
Glenn RayExcel VBA DeveloperCommented:
Those PivotTables are using Grouping to consolidate the Order Date values into months and years.  When Grouping on dates is used new fields like "Years" will appear allowing one to filter on the year values instead.  It also will create two additional filtering values - for dates before or after all dates in the raw data.

So, if you want to get rid of them, you have to first add back the Order Date into the Row Labels for each PivotTable. This isn't possible for the first PivotTable because trying to do that will result in an error
overlap error message
So, you'll have to remove the second PivotTable first, edit the first one and then copy it or re-create the second one.

1) Delete the second PivotTable
2) On remaining PivotTable, select "Order Date" from the fields.  It should drop into the Row Labels box by default.  However, what you'll see are month abbreviations below each Salesperson name - not actual dates!
3) Right-click on any month abbreviation and click "Ungroup".  They will now appear as dates, AND the "Years" field will disappear!
4) Remove the "Order Date" field from the Row Labels.

Regards,
-Glenn
0
 
Frank FreeseAuthor Commented:
Thanks Glenn - I got educated!
0
 
Frank FreeseAuthor Commented:
Thank you very much
0
 
Glenn RayExcel VBA DeveloperCommented:
I got some education, too.  I thought I had a solution, then walked away for a bit and started thinking about Calculated Fields..and then realized Groups would do this.
0
 
Frank FreeseAuthor Commented:
Ah well...keep the points :)
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now