Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Pivot table created

Posted on 2014-09-20
5
113 Views
Last Modified: 2014-09-20
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
Comment
Question by:Frank Freese
  • 3
  • 2
5 Comments
 
LVL 27

Accepted Solution

by:
Glenn Ray earned 500 total points
ID: 40334802
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
 

Author Comment

by:Frank Freese
ID: 40334817
Thanks Glenn - I got educated!
0
 

Author Closing Comment

by:Frank Freese
ID: 40334818
Thank you very much
0
 
LVL 27

Expert Comment

by:Glenn Ray
ID: 40334820
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
 

Author Comment

by:Frank Freese
ID: 40334821
Ah well...keep the points :)
0

Featured Post

Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Introduction This Article is a follow-up to my Mappit! Addin Article (http://www.experts-exchange.com/A_2613.html), it was inspired by an email posting I made to EUSPRIG (http://www.eusprig.org/index.htm), I will briefly cover: 1) An overvieā€¦
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calculā€¦
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question