Excel Pivot Chart - How do I make a data field a drop down?

[SVO4Turbo]

Ok, I'm making a bunch of interactive charts for a department with my company.

I have 15 data elements that I need to report performance on. These data elements lie across 8 different groups of people.

The pivot table behind the chart is a simple one. Page headers include fields called (Today Team, Month End Team). This accounts for people that changed teams.

Column Heading is (ReportDate) and shows month end dates for 13 months.

Row Heading is (Rep Name) which accounts for all representatives within my organization.

Data field is what I'm having problems with. I have 15 data elements that I want to report on, but want them to be able to select them from a drop down list on the pivot chart, so I don't have to make 15 pivot charts, with the individual data elements listed. I want one chart that they can select different data elements from the pivot chart.

These people are end users and I DON'T want them trying to modify the pivot table by adding/removing fields directly.

HELP!!

 

[KenWright]

So why have them do anything? Throw that element into the page fields and using the 'Show Pages' icon on the Pivot Toolbar, create a page for every element. It's automatic and all you do is hit the button. They just have to select the right sheet for that element

Regards
Ken.................

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

----------------------------------------------------------------------------

 

[SVO4Turbo]

I agree, that step might work in a lot of applications, but in this case, there are too many variables to create individual sheets for each performance metric.

My goal is to allow the user to select various ways to look at the data from one pivot chart.

 

[Wicca]

Auto-filter?

Merry Part and the Brightest of Blessings

With respect
Wicca
-----
IF you think you can
Or if you think you can't
Then you are probably right
-Henry Ford

 

[SVO4Turbo]

Wicca,

What exactly do you mean? I'm open to suggestions, just need a little more explanation.

Maybe it would help if I gave examples.

Source Data

Team ReportDate RepName Calls Hours Kept
A 03/31/03 John Smith 150 100 25
A 02/29/03 John Smith 125 110 30
A 01/31/03 John Smith 100 120 22
B 03/31/03 John Doe 105 105 15
B 02/29/03 John Doe 144 122 23

OK. I use Team as a page header. That leaves a drop down on the pivot chart so that the end user can select between team A, B, or All. Then, I use ReportDate as the Column Header on the pivot table. That allows the report date field to show on the Right hand side of the pivot chart with a drop down, so the user can select from different dates. I use Rep name as the Row Heading. This allows all of the reps for a particular team to display when the Team value from the page header is selected. This all works very well.

Now the problem comes in when I get to the Details. I want a drop down for Calls, Hours, and Kept, so that the end user can change what data the chart is reporting. The only way to accomplish this as far as I can tell now, is to create 3 different pivot tables, with 3 different pivot charts, each representative of one of the data values. In the real world, there's 15 elements and I don't want to use 15 different pivot tables and pivot charts. I want 1 pivot chart and table that can accomplish this.

Maybe that clears it up a bit. Any suggestions?

 

[Wicca]

I am not sure it is what you want
but if you have a spreedsheet with the data on

Highlight a cell somewhere in the data

go to MenuBar......Data..AutoFilter

This will convert all the columns into dropdown filters
allowing the users to choose by name, team, calls or whatever

Hope that helps



Merry Part and the Brightest of Blessings

With respect
Wicca
-----
IF you think you can
Or if you think you can't
Then you are probably right
-Henry Ford

 

[SVO4Turbo]

Wicca

I see what that does. It allows you to filter based on the values in each particular column. I'm looking for a drop down to select which column to show.

Any other suggestions?

 

[fkegley]

What about a macro that presents the user with a choice of Details and then invokes the Pivot Table command using their choice as the Details element.

You can record creating a pivot table via a macro. You would then need to put instructions ahead of the Pivot Table creation instruction that would allow the users to select Calls, Hours, or Kept. (I would probably an Input Box for this, although you could create a form.) You would then need to tinker with the instructions that create the Pivot Table so that it will use their response as the Details.

Frank kegley
fkegley@hotmail.com

 

[SVO4Turbo]

fkegley-

I agree, I'm in the process of making macros to add and remove data elements from the details section. I plan to have a pushbutton interface for them. I don't know of any other way to make this happen.

Thanks