Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old June 27th 08, 08:03 PM posted to microsoft.public.excel.setup
external usenet poster
 
First recorded activity by ExcelBanter: Sep 2006
Posts: 108
Default Removing "Sum of" from pivot tables

In 2003, we had a formatting option that would globally remove “Sum Of” from
the pivot table values. I like that feature. However, I can’t seem to find
the same thing in 2007. I can manually change them, but that’s no fun,
especially if I have a bunch of values.

Does anyone have solution to this question?

Thanks


  #2   Report Post  
Old June 28th 08, 12:17 AM posted to microsoft.public.excel.setup
external usenet poster
 
First recorded activity by ExcelBanter: Feb 2008
Posts: 168
Default Removing "Sum of" from pivot tables

Hi Russ,

I don't know which command you mean but maybe one
of these sites might help you out.

http://www.rondebruin.nl/0307commands.htm
http://office.microsoft.com/en-us/he...491511033.aspx

HTH
Martin


"Russ" wrote in message
...
In 2003, we had a formatting option that would globally remove "Sum Of"
from
the pivot table values. I like that feature. However, I can't seem to
find
the same thing in 2007. I can manually change them, but that's no fun,
especially if I have a bunch of values.

Does anyone have solution to this question?

Thanks



  #3   Report Post  
Old June 28th 08, 10:09 AM posted to microsoft.public.excel.setup
external usenet poster
 
First recorded activity by ExcelBanter: Jun 2008
Posts: 1
Default Removing "Sum of" from pivot tables


I can't help but I'd love to know how to access this in Excel 2003!
Its not an option I am aware of.

Mike



On Jun 27, 8:03*pm, Russ wrote:
In 2003, we had a formatting option that would globally remove Sum Of from
the pivot table values. *I like that feature. *However, I cant seem to find
the same thing in 2007. *I can manually change them, but thats no fun,
especially if I have a bunch of values. *

Does anyone have solution to this question?

Thanks


  #4   Report Post  
Old June 28th 08, 11:42 AM posted to microsoft.public.excel.setup
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2007
Posts: 2,480
Default Removing "Sum of" from pivot tables

Hi Russ

I am totally unaware of any in built function in XL2003 that would do this,
and there is no such function in XL2007.
However, the following code will achieve what you want.

Sub ChangePTName()
Dim pt As PivotTable, pf As PivotField, ws As Worksheet, i As Long
Application.ScreenUpdating = False
Set ws = ActiveSheet
For i = 1 To ws.PivotTables.Count
Set pt = ws.PivotTables(i)
pt.ManualUpdate = True
For Each pf In pt.DataFields
If pf.Function = xlSum Then
If Left(pf.Caption, 6) = "Sum of" Then
pf.Caption = Right(pf.Caption, Len(pf.Caption) - 6)
End If
End If
Next
pt.ManualUpdate = False
Next i
Application.ScreenUpdating = True
End Sub

Copy the Code above
Alt+F11 to invoke the VB Editor
InsertModule
Paste code into white pane that appears
Alt+F11 to return to Excel

To use
Select sheet containing the PT's
Alt+F8 to bring up Macros
Highlight ChangePTName
Run

--
Regards
Roger Govier

"Russ" wrote in message
...
In 2003, we had a formatting option that would globally remove “Sum Of”
from
the pivot table values. I like that feature. However, I can’t seem to
find
the same thing in 2007. I can manually change them, but that’s no fun,
especially if I have a bunch of values.

Does anyone have solution to this question?

Thanks

  #5   Report Post  
Old June 28th 08, 07:10 PM posted to microsoft.public.excel.setup
external usenet poster
 
First recorded activity by ExcelBanter: Sep 2006
Posts: 108
Default Removing "Sum of" from pivot tables

Thanks. I will give this a try.

To all, the Pivot table autoformat functionality in Excel 2003 would do what
I'm discussing. For example, the autoformat would display "Expenses" versus
"Sum of Expenses". There was 20 autoformats available in addition to Classic
and None.

This is what I'm looking for. My users don't need to see "Sum of". That's
obvious to them. These can be manually removed, but there should be a global
functionality that was in Excel 2003.

"Roger Govier" wrote:

Hi Russ

I am totally unaware of any in built function in XL2003 that would do this,
and there is no such function in XL2007.
However, the following code will achieve what you want.

Sub ChangePTName()
Dim pt As PivotTable, pf As PivotField, ws As Worksheet, i As Long
Application.ScreenUpdating = False
Set ws = ActiveSheet
For i = 1 To ws.PivotTables.Count
Set pt = ws.PivotTables(i)
pt.ManualUpdate = True
For Each pf In pt.DataFields
If pf.Function = xlSum Then
If Left(pf.Caption, 6) = "Sum of" Then
pf.Caption = Right(pf.Caption, Len(pf.Caption) - 6)
End If
End If
Next
pt.ManualUpdate = False
Next i
Application.ScreenUpdating = True
End Sub

Copy the Code above
Alt+F11 to invoke the VB Editor
InsertModule
Paste code into white pane that appears
Alt+F11 to return to Excel

To use
Select sheet containing the PT's
Alt+F8 to bring up Macros
Highlight ChangePTName
Run

--
Regards
Roger Govier

"Russ" wrote in message
...
In 2003, we had a formatting option that would globally remove “Sum Of”
from
the pivot table values. I like that feature. However, I can’t seem to
find
the same thing in 2007. I can manually change them, but that’s no fun,
especially if I have a bunch of values.

Does anyone have solution to this question?

Thanks



  #6   Report Post  
Old June 28th 08, 07:11 PM posted to microsoft.public.excel.setup
external usenet poster
 
First recorded activity by ExcelBanter: Sep 2006
Posts: 108
Default Removing "Sum of" from pivot tables

Thanks for the references. I'll check them out.

To all, the Pivot table autoformat functionality in Excel 2003 would do what
I'm discussing. For example, the autoformat would display "Expenses" versus
"Sum of Expenses". There was 20 autoformats available in addition to Classic
and None.

This is what I'm looking for. My users don't need to see "Sum of". That's
obvious to them. These can be manually removed, but there should be a global
functionality that was in Excel 2003.


"MartinW" wrote:

Hi Russ,

I don't know which command you mean but maybe one
of these sites might help you out.

http://www.rondebruin.nl/0307commands.htm
http://office.microsoft.com/en-us/he...491511033.aspx

HTH
Martin


"Russ" wrote in message
...
In 2003, we had a formatting option that would globally remove "Sum Of"
from
the pivot table values. I like that feature. However, I can't seem to
find
the same thing in 2007. I can manually change them, but that's no fun,
especially if I have a bunch of values.

Does anyone have solution to this question?

Thanks




  #7   Report Post  
Old June 28th 08, 07:12 PM posted to microsoft.public.excel.setup
external usenet poster
 
First recorded activity by ExcelBanter: Sep 2006
Posts: 108
Default Removing "Sum of" from pivot tables

Hi Martin,

The Pivot table autoformat functionality in Excel 2003 would do what I'm
discussing. For example, the autoformat would display "Expenses" versus "Sum
of Expenses". There was 20 autoformats available in addition to Classic and
None.

Try one of these and you'll see what I'm talking about.

Thanks.

Russ

"MartinW" wrote:

Hi Russ,

I don't know which command you mean but maybe one
of these sites might help you out.

http://www.rondebruin.nl/0307commands.htm
http://office.microsoft.com/en-us/he...491511033.aspx

HTH
Martin


"Russ" wrote in message
...
In 2003, we had a formatting option that would globally remove "Sum Of"
from
the pivot table values. I like that feature. However, I can't seem to
find
the same thing in 2007. I can manually change them, but that's no fun,
especially if I have a bunch of values.

Does anyone have solution to this question?

Thanks




  #8   Report Post  
Old June 29th 08, 05:05 PM posted to microsoft.public.excel.setup
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2007
Posts: 2,480
Default Removing "Sum of" from pivot tables

Hi Russ

Since I seldom, if ever, use Autoformat, I was totally unaware that it would
remove the "Sum of".
Thanks for pointing that out.
The macro I posted should achieve what you want

You will note that the macro leaves a space in front of the field name.
That is quite deliberate, as you cannot have a PT field name the same as a
Source data field name in the Data area - the reason why Sum of and Count of
etc. are added in the first instance.
You can easily amend the code to deal with Count of etc., should you have
the need.
--
Regards
Roger Govier

"Russ" wrote in message
...
Thanks. I will give this a try.

To all, the Pivot table autoformat functionality in Excel 2003 would do
what
I'm discussing. For example, the autoformat would display "Expenses"
versus
"Sum of Expenses". There was 20 autoformats available in addition to
Classic
and None.

This is what I'm looking for. My users don't need to see "Sum of".
That's
obvious to them. These can be manually removed, but there should be a
global
functionality that was in Excel 2003.

"Roger Govier" wrote:

Hi Russ

I am totally unaware of any in built function in XL2003 that would do
this,
and there is no such function in XL2007.
However, the following code will achieve what you want.

Sub ChangePTName()
Dim pt As PivotTable, pf As PivotField, ws As Worksheet, i As Long
Application.ScreenUpdating = False
Set ws = ActiveSheet
For i = 1 To ws.PivotTables.Count
Set pt = ws.PivotTables(i)
pt.ManualUpdate = True
For Each pf In pt.DataFields
If pf.Function = xlSum Then
If Left(pf.Caption, 6) = "Sum of" Then
pf.Caption = Right(pf.Caption, Len(pf.Caption) - 6)
End If
End If
Next
pt.ManualUpdate = False
Next i
Application.ScreenUpdating = True
End Sub

Copy the Code above
Alt+F11 to invoke the VB Editor
InsertModule
Paste code into white pane that appears
Alt+F11 to return to Excel

To use
Select sheet containing the PT's
Alt+F8 to bring up Macros
Highlight ChangePTName
Run

--
Regards
Roger Govier

"Russ" wrote in message
...
In 2003, we had a formatting option that would globally remove “Sum Of”
from
the pivot table values. I like that feature. However, I can’t seem to
find
the same thing in 2007. I can manually change them, but that’s no fun,
especially if I have a bunch of values.

Does anyone have solution to this question?

Thanks

  #9   Report Post  
Old June 30th 08, 08:31 PM posted to microsoft.public.excel.setup
external usenet poster
 
First recorded activity by ExcelBanter: Sep 2006
Posts: 108
Default Removing "Sum of" from pivot tables

Thanks. I'll give this a whirl.

Cheers,

Russ

"Roger Govier" wrote:

Hi Russ

Since I seldom, if ever, use Autoformat, I was totally unaware that it would
remove the "Sum of".
Thanks for pointing that out.
The macro I posted should achieve what you want

You will note that the macro leaves a space in front of the field name.
That is quite deliberate, as you cannot have a PT field name the same as a
Source data field name in the Data area - the reason why Sum of and Count of
etc. are added in the first instance.
You can easily amend the code to deal with Count of etc., should you have
the need.
--
Regards
Roger Govier

"Russ" wrote in message
...
Thanks. I will give this a try.

To all, the Pivot table autoformat functionality in Excel 2003 would do
what
I'm discussing. For example, the autoformat would display "Expenses"
versus
"Sum of Expenses". There was 20 autoformats available in addition to
Classic
and None.

This is what I'm looking for. My users don't need to see "Sum of".
That's
obvious to them. These can be manually removed, but there should be a
global
functionality that was in Excel 2003.

"Roger Govier" wrote:

Hi Russ

I am totally unaware of any in built function in XL2003 that would do
this,
and there is no such function in XL2007.
However, the following code will achieve what you want.

Sub ChangePTName()
Dim pt As PivotTable, pf As PivotField, ws As Worksheet, i As Long
Application.ScreenUpdating = False
Set ws = ActiveSheet
For i = 1 To ws.PivotTables.Count
Set pt = ws.PivotTables(i)
pt.ManualUpdate = True
For Each pf In pt.DataFields
If pf.Function = xlSum Then
If Left(pf.Caption, 6) = "Sum of" Then
pf.Caption = Right(pf.Caption, Len(pf.Caption) - 6)
End If
End If
Next
pt.ManualUpdate = False
Next i
Application.ScreenUpdating = True
End Sub

Copy the Code above
Alt+F11 to invoke the VB Editor
InsertModule
Paste code into white pane that appears
Alt+F11 to return to Excel

To use
Select sheet containing the PT's
Alt+F8 to bring up Macros
Highlight ChangePTName
Run

--
Regards
Roger Govier

"Russ" wrote in message
...
In 2003, we had a formatting option that would globally remove “Sum Of”
from
the pivot table values. I like that feature. However, I can’t seem to
find
the same thing in 2007. I can manually change them, but that’s no fun,
especially if I have a bunch of values.

Does anyone have solution to this question?

Thanks



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Removing "Sum of" from pivot tables Russ Excel Discussion (Misc queries) 4 June 29th 08 12:01 AM
Pivot Tables show actual name rather then "Column Labels" SQL2005_rocks Excel Discussion (Misc queries) 5 February 13th 07 05:15 PM
Avoid "update Links" promt in e-mailed pivot tables? Michelle B Excel Discussion (Misc queries) 2 October 23rd 06 02:54 PM
Pivot Tables - How can I "reset" the selections in "Row Field"? shadestreet Excel Discussion (Misc queries) 3 April 24th 06 06:29 PM
How do I disable "Get Pivot Data" when working from pivot tables? Frustrated excel 2003 user Excel Worksheet Functions 2 November 29th 05 12:00 AM


All times are GMT +1. The time now is 01:15 AM.

Powered by vBulletin® Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
Copyright 2004-2019 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017