Solved

Pivot table created

Posted on 2014-09-20
5
112 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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

777 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