Pivot - Select column data

[brutus6294]

I have a pivottable with Year and Quarter in the Column Data of the pivot table. Is there a way for an end user to not display Quarter information at all if they just want results by year? I've tried de-selecting Quarters using the pull-down, but it seems at least one quarter must be selected. Any thoughts?

 

[GlennUK]

What about dragging the Quarter field off the PivotTable?

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[SkipVought]

If you have DATES and not separate Year and Quarter, in the PT, you can GROUP by Year & Quarter or just YEAR.

Skip,
[sub]
When a group touring the Crest Toothpaste factory got caught in a large cooler, headlines read...
Tooth Company Freeze a Crowd! and
Many are Cold, but Few are Frozen![/sub]

 

[brutus6294]

Thanks, I've thought of that, but my hope is to make a pivottable accessible to a group of users. That's easy enough to do, but not sure I want the end users changing the form of the pivottable.. Next guy who looks at the pivottable may not realize that he needs to drag qtr's back in if he wants to see it. I was hoping to keep them confined to selecting data by using the drop-down boxes.

 

[BlueHorizon]

Hi,
If your users can use the PitvotTable toolbar, they can Show/Hide detail (Plus/Minus icons) in column areas with more than one field.


Best,
Blue Horizon

 

[brutus6294]

Blue Horizon,
That sounds like what I was looking for. If I go up to PivotTable menu and select Hide Detail, it removes the QTR entries. However, when I want to put it back, I get a "Choose the field containing the detail you want to show" with a list of fields. However QTR is not listed among them. This would work well if I could easily toggle on off, but I'm not sure how to get QTR data to show again using this method

 

[zahead31]

brutus,
I did a similar task with pivot tables through VBA. Use the macro recorder to record yourself removing the the QTR field and adding the (and formatting) the QTR field. You can customize this code (thorugh the VBE) a bit and assign it to toggle buttons.

If you have any specific questions after you record your macro, you can open a thread in the VBA Visual Basic for Applications (Microsoft) Forum.

 

[brutus6294]

Thanks zahead32..
That was pretty good... My problem is I have probably 20 different Pivottables in one excel file. Your solution worked with one pivot that I created the Macro for, but would not work on others....I'm not familiar with VBA so forgive my ignorance..

 

[SkipVought]

"...20 different Pivottables..."

SIMILAR PivotTables, it seems, if you have to do the same things to each one.

That is different about each PT?

Why not ONE PivotTable with selections?

Skip,
[sub]
When a group touring the Crest Toothpaste factory got caught in a large cooler, headlines read...
Tooth Company Freeze a Crowd! and
Many are Cold, but Few are Frozen![/sub]

 

[brutus6294]

SkipVought,
Possibly because of my ignorance..
There are 10-20 different pivottables based off of the same data file. Most are on different tabs of the same excel file. Each one has it's own unique formatting. For example one may be sales by state or sales by category... one showing detail, others not..There's probably a better way to organize but this is what the user want and I didn't know better to suggest a better way to present it.

 

[SkipVought]

Although they are from the same source data, the output fields are different. It makes some sense to make them different PTs, althought I might consider doing them on ONE PT, since I know I could control the selections and formats in VBA. But if you're not strong in VBA, then separate PTs on separate sheets is an okay approch, IMHO.

However, this requirement runs closer to ONE then MANY, since you are wanting to do what zahead32 suggested. Fact is, though, with a macro, you COULD do it on MANY. It's a matter of preference and approch.

Skip,
[sub]
When a group touring the Crest Toothpaste factory got caught in a large cooler, headlines read...
Tooth Company Freeze a Crowd! and
Many are Cold, but Few are Frozen![/sub]

 

[brutus6294]

I think I was able to get what I needed using BlueHorizons suggestion. The other solutions I'm sure would be fine also but BlueHorizon's is very simple and seems to work for me..(so far) Thanks All!!!