Solved

Pivot table created

Posted on 2014-09-20
5
111 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
Comment Utility
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
Comment Utility
Thanks Glenn - I got educated!
0
 

Author Closing Comment

by:Frank Freese
Comment Utility
Thank you very much
0
 
LVL 27

Expert Comment

by:Glenn Ray
Comment Utility
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
Comment Utility
Ah well...keep the points :)
0

Featured Post

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

INDEX and MATCH can be used to great effect to replace HLOOKUP and VLOOKUP as it does not have the limitation of needing the data to be sorted so that the reference value is in the first column or row. It also has the ability to perform a bi-directi…
Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

728 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now