PIVOT - Using a cell outside the table for change the grouping

Hi experts,

I want to enable users with few knowledge of pivot techniques to
change the grouping of a pivot chart resp. the underlying pivot table.
The idea is, to have a changeable cell value beneath the chart to
enter the group (groups of 2, 4, 6, 5, etc...). The associated pivot
table shall change it's groupings accordingly, thus forces to change
the associated chart.

Any idea, how to achieve this?
Thanks and have a nice day
Michael
0
emte69 (54)
2/13/2009 7:59:36 PM
excel 39879 articles. 2 followers. Follow

11 Replies
914 Views

Similar Articles

[PageSpeed] 40

Hi,

What kind of groupings are we talking about when we say 2, 4, 6, 5?

What is being grouped and how?  What fields, row fields, more than one row 
field, column fields, both row and column fields?  Are these fields groups on 
Month, Day (if so how many), Year, Hour, Minute, Second, Numbers?  Or are 
these manual groupings?  Are these groupings those generated by the Grouping 
command or by anding other row or column fields, or by checking and 
unchecking filter options?


-- 
If this helps, please click the Yes button

Cheers,
Shane Devenshire


"Michael.Tarnowski" wrote:

> Hi experts,
> 
> I want to enable users with few knowledge of pivot techniques to
> change the grouping of a pivot chart resp. the underlying pivot table.
> The idea is, to have a changeable cell value beneath the chart to
> enter the group (groups of 2, 4, 6, 5, etc...). The associated pivot
> table shall change it's groupings accordingly, thus forces to change
> the associated chart.
> 
> Any idea, how to achieve this?
> Thanks and have a nice day
> Michael
> 
0
2/13/2009 9:24:18 PM
On Feb 13, 10:24 pm, Shane Devenshire
<ShaneDevensh...@discussions.microsoft.com> wrote:
> Hi,
>
> What kind of groupings are we talking about when we say 2, 4, 6, 5?
>
> What is being grouped and how?  What fields, row fields, more than one row
> field, column fields, both row and column fields?  Are these fields groups on
> Month, Day (if so how many), Year, Hour, Minute, Second, Numbers?  Or are
> these manual groupings?  Are these groupings those generated by the Grouping
> command or by anding other row or column fields, or by checking and
> unchecking filter options?
>
> --
> If this helps, please click the Yes button
>
> Cheers,
> Shane Devenshire
>
> "Michael.Tarnowski" wrote:
> > Hi experts,
>
> > I want to enable users with few knowledge of pivot techniques to
> > change the grouping of a pivot chart resp. the underlying pivot table.
> > The idea is, to have a changeable cell value beneath the chart to
> > enter the group (groups of 2, 4, 6, 5, etc...). The associated pivot
> > table shall change it's groupings accordingly, thus forces to change
> > the associated chart.
>
> > Any idea, how to achieve this?
> > Thanks and have a nice day
> > Michael

Hi Shane,
thanks for your answer.
It is a manually grouping. Here is a picture:
http://michaeltarnowski.de/temp/Clipboard02.gif

Michael
0
emte69 (54)
2/14/2009 3:51:49 PM
On Feb 13, 10:24 pm, Shane Devenshire
<ShaneDevensh...@discussions.microsoft.com> wrote:
> Hi,
>
> What kind of groupings are we talking about when we say 2, 4, 6, 5?
>
> What is being grouped and how?  What fields, row fields, more than one row
> field, column fields, both row and column fields?  Are these fields groups on
> Month, Day (if so how many), Year, Hour, Minute, Second, Numbers?  Or are
> these manual groupings?  Are these groupings those generated by the Grouping
> command or by anding other row or column fields, or by checking and
> unchecking filter options?
>
> --
> If this helps, please click the Yes button
>
> Cheers,
> Shane Devenshire
>
> "Michael.Tarnowski" wrote:
> > Hi experts,
>
> > I want to enable users with few knowledge of pivot techniques to
> > change the grouping of a pivot chart resp. the underlying pivot table.
> > The idea is, to have a changeable cell value beneath the chart to
> > enter the group (groups of 2, 4, 6, 5, etc...). The associated pivot
> > table shall change it's groupings accordingly, thus forces to change
> > the associated chart.
>
> > Any idea, how to achieve this?
> > Thanks and have a nice day
> > Michael

Hi Shane,
thanks for your answer.
It is a manually grouping. Here is a picture:
http://michaeltarnowski.de/temp/Clipboard02.gif

Michael
0
emte69 (54)
2/14/2009 3:52:17 PM
Hi,

I see the picture, but I don't see 2, 4, 6, or 5 as option or values 
anywhere in the picture.  So based on the picture, give me an example of what 
you want to have happen.  I see the "before"  what will it look like "after"?

Also, before I go too much further, keep in mind that the pivot table will 
not change just by the user entering a number in a cell.  For that to happen 
you will need to add VBA code.  Second, if the process, whatever it may be, 
turns out to be fairly simple, I think you should teach the users how to do 
it.  One of the problems with programming is that when you leave, if no one 
else is a programmer and something goes wrong - nobody has a clue.

-- 
If this helps, please click the Yes button

Cheers,
Shane Devenshire


"Michael.Tarnowski" wrote:

> On Feb 13, 10:24 pm, Shane Devenshire
> <ShaneDevensh...@discussions.microsoft.com> wrote:
> > Hi,
> >
> > What kind of groupings are we talking about when we say 2, 4, 6, 5?
> >
> > What is being grouped and how?  What fields, row fields, more than one row
> > field, column fields, both row and column fields?  Are these fields groups on
> > Month, Day (if so how many), Year, Hour, Minute, Second, Numbers?  Or are
> > these manual groupings?  Are these groupings those generated by the Grouping
> > command or by anding other row or column fields, or by checking and
> > unchecking filter options?
> >
> > --
> > If this helps, please click the Yes button
> >
> > Cheers,
> > Shane Devenshire
> >
> > "Michael.Tarnowski" wrote:
> > > Hi experts,
> >
> > > I want to enable users with few knowledge of pivot techniques to
> > > change the grouping of a pivot chart resp. the underlying pivot table.
> > > The idea is, to have a changeable cell value beneath the chart to
> > > enter the group (groups of 2, 4, 6, 5, etc...). The associated pivot
> > > table shall change it's groupings accordingly, thus forces to change
> > > the associated chart.
> >
> > > Any idea, how to achieve this?
> > > Thanks and have a nice day
> > > Michael
> 
> Hi Shane,
> thanks for your answer.
> It is a manually grouping. Here is a picture:
> http://michaeltarnowski.de/temp/Clipboard02.gif
> 
> Michael
> 
0
2/15/2009 12:19:01 AM
On Feb 15, 1:19 am, Shane Devenshire
<ShaneDevensh...@discussions.microsoft.com> wrote:
> Hi,
>
> I see the picture, but I don't see 2, 4, 6, or 5 as option or values
> anywhere in the picture.  So based on the picture, give me an example of what
> you want to have happen.  I see the "before"  what will it look like "after"?
>
> Also, before I go too much further, keep in mind that the pivot table will
> not change just by the user entering a number in a cell.  For that to happen
> you will need to add VBA code.  Second, if the process, whatever it may be,
> turns out to be fairly simple, I think you should teach the users how to do
> it.  One of the problems with programming is that when you leave, if no one
> else is a programmer and something goes wrong - nobody has a clue.
>
> --
> If this helps, please click the Yes button
>
> Cheers,
> Shane Devenshire
>
> "Michael.Tarnowski" wrote:
> > On Feb 13, 10:24 pm, Shane Devenshire
> > <ShaneDevensh...@discussions.microsoft.com> wrote:
> > > Hi,
>
> > > What kind of groupings are we talking about when we say 2, 4, 6, 5?
>
> > > What is being grouped and how?  What fields, row fields, more than one row
> > > field, column fields, both row and column fields?  Are these fields groups on
> > > Month, Day (if so how many), Year, Hour, Minute, Second, Numbers?  Or are
> > > these manual groupings?  Are these groupings those generated by the Grouping
> > > command or by anding other row or column fields, or by checking and
> > > unchecking filter options?
>
> > > --
> > > If this helps, please click the Yes button
>
> > > Cheers,
> > > Shane Devenshire
>
> > > "Michael.Tarnowski" wrote:
> > > > Hi experts,
>
> > > > I want to enable users with few knowledge of pivot techniques to
> > > > change the grouping of a pivot chart resp. the underlying pivot table.
> > > > The idea is, to have a changeable cell value beneath the chart to
> > > > enter the group (groups of 2, 4, 6, 5, etc...). The associated pivot
> > > > table shall change it's groupings accordingly, thus forces to change
> > > > the associated chart.
>
> > > > Any idea, how to achieve this?
> > > > Thanks and have a nice day
> > > > Michael
>
> > Hi Shane,
> > thanks for your answer.
> > It is a manually grouping. Here is a picture:
> >http://michaeltarnowski.de/temp/Clipboard02.gif
>
> > Michael

Shane,
the grouping happens in the field "weeks open". As you see, "weeks
open" is currently grouped by 5 (manual click in pivot table, then a
right click, "Group and Show Detail>Group").
I'am looking for a VBA code to change this grouping by changing a cell
value outside the pivot table only  -   thus, no right click in the
table, no more menu selection of "Group and Show Detail>Group", etc.,
-- only entering a value.
Michael
0
emte69 (54)
2/15/2009 2:52:00 AM
-- 
If this helps, please click the Yes button

Cheers,
Shane Devenshire

 
"Michael.Tarnowski" wrote:

> On Feb 15, 1:19 am, Shane Devenshire
> <ShaneDevensh...@discussions.microsoft.com> wrote:
> > Hi,
> >
> > I see the picture, but I don't see 2, 4, 6, or 5 as option or values
> > anywhere in the picture.  So based on the picture, give me an example of what
> > you want to have happen.  I see the "before"  what will it look like "after"?
> >
> > Also, before I go too much further, keep in mind that the pivot table will
> > not change just by the user entering a number in a cell.  For that to happen
> > you will need to add VBA code.  Second, if the process, whatever it may be,
> > turns out to be fairly simple, I think you should teach the users how to do
> > it.  One of the problems with programming is that when you leave, if no one
> > else is a programmer and something goes wrong - nobody has a clue.
> >
> > --
> > If this helps, please click the Yes button
> >
> > Cheers,
> > Shane Devenshire
> >
> > "Michael.Tarnowski" wrote:
> > > On Feb 13, 10:24 pm, Shane Devenshire
> > > <ShaneDevensh...@discussions.microsoft.com> wrote:
> > > > Hi,
> >
> > > > What kind of groupings are we talking about when we say 2, 4, 6, 5?
> >
> > > > What is being grouped and how?  What fields, row fields, more than one row
> > > > field, column fields, both row and column fields?  Are these fields groups on
> > > > Month, Day (if so how many), Year, Hour, Minute, Second, Numbers?  Or are
> > > > these manual groupings?  Are these groupings those generated by the Grouping
> > > > command or by anding other row or column fields, or by checking and
> > > > unchecking filter options?
> >
> > > > --
> > > > If this helps, please click the Yes button
> >
> > > > Cheers,
> > > > Shane Devenshire
> >
> > > > "Michael.Tarnowski" wrote:
> > > > > Hi experts,
> >
> > > > > I want to enable users with few knowledge of pivot techniques to
> > > > > change the grouping of a pivot chart resp. the underlying pivot table.
> > > > > The idea is, to have a changeable cell value beneath the chart to
> > > > > enter the group (groups of 2, 4, 6, 5, etc...). The associated pivot
> > > > > table shall change it's groupings accordingly, thus forces to change
> > > > > the associated chart.
> >
> > > > > Any idea, how to achieve this?
> > > > > Thanks and have a nice day
> > > > > Michael
> >
> > > Hi Shane,
> > > thanks for your answer.
> > > It is a manually grouping. Here is a picture:
> > >http://michaeltarnowski.de/temp/Clipboard02.gif
> >
> > > Michael
> 
> Shane,
> the grouping happens in the field "weeks open". As you see, "weeks
> open" is currently grouped by 5 (manual click in pivot table, then a
> right click, "Group and Show Detail>Group").
> I'am looking for a VBA code to change this grouping by changing a cell
> value outside the pivot table only  -   thus, no right click in the
> table, no more menu selection of "Group and Show Detail>Group", etc.,
> -- only entering a value.
> Michael
> 
0
2/15/2009 4:29:01 AM
On Feb 15, 5:29 am, Shane Devenshire
<ShaneDevensh...@discussions.microsoft.com> wrote:
> --
> If this helps, please click the Yes button
>
> Cheers,
> Shane Devenshire
>
> "Michael.Tarnowski" wrote:
> > On Feb 15, 1:19 am, Shane Devenshire
> > <ShaneDevensh...@discussions.microsoft.com> wrote:
> > > Hi,
>
> > > I see the picture, but I don't see 2, 4, 6, or 5 as option or values
> > > anywhere in the picture.  So based on the picture, give me an example of what
> > > you want to have happen.  I see the "before"  what will it look like "after"?
>
> > > Also, before I go too much further, keep in mind that the pivot table will
> > > not change just by the user entering a number in a cell.  For that to happen
> > > you will need to add VBA code.  Second, if the process, whatever it may be,
> > > turns out to be fairly simple, I think you should teach the users how to do
> > > it.  One of the problems with programming is that when you leave, if no one
> > > else is a programmer and something goes wrong - nobody has a clue.
>
> > > --
> > > If this helps, please click the Yes button
>
> > > Cheers,
> > > Shane Devenshire
>
> > > "Michael.Tarnowski" wrote:
> > > > On Feb 13, 10:24 pm, Shane Devenshire
> > > > <ShaneDevensh...@discussions.microsoft.com> wrote:
> > > > > Hi,
>
> > > > > What kind of groupings are we talking about when we say 2, 4, 6, 5?
>
> > > > > What is being grouped and how?  What fields, row fields, more than one row
> > > > > field, column fields, both row and column fields?  Are these fields groups on
> > > > > Month, Day (if so how many), Year, Hour, Minute, Second, Numbers?  Or are
> > > > > these manual groupings?  Are these groupings those generated by the Grouping
> > > > > command or by anding other row or column fields, or by checking and
> > > > > unchecking filter options?
>
> > > > > --
> > > > > If this helps, please click the Yes button
>
> > > > > Cheers,
> > > > > Shane Devenshire
>
> > > > > "Michael.Tarnowski" wrote:
> > > > > > Hi experts,
>
> > > > > > I want to enable users with few knowledge of pivot techniques to
> > > > > > change the grouping of a pivot chart resp. the underlying pivot table.
> > > > > > The idea is, to have a changeable cell value beneath the chart to
> > > > > > enter the group (groups of 2, 4, 6, 5, etc...). The associated pivot
> > > > > > table shall change it's groupings accordingly, thus forces to change
> > > > > > the associated chart.
>
> > > > > > Any idea, how to achieve this?
> > > > > > Thanks and have a nice day
> > > > > > Michael
>
> > > > Hi Shane,
> > > > thanks for your answer.
> > > > It is a manually grouping. Here is a picture:
> > > >http://michaeltarnowski.de/temp/Clipboard02.gif
>
> > > > Michael
>
> > Shane,
> > the grouping happens in the field "weeks open". As you see, "weeks
> > open" is currently grouped by 5 (manual click in pivot table, then a
> > right click, "Group and Show Detail>Group").
> > I'am looking for a VBA code to change this grouping by changing a cell
> > value outside the pivot table only  -   thus, no right click in the
> > table, no more menu selection of "Group and Show Detail>Group", etc.,
> > -- only entering a value.
> > Michael

Hi community,
I found a solution:

if worksheet A holds the pivot table, and worksheetB the chart and a
cell names "groups" and C13 is a cell in the pivot table.

Sub PivotGrouping()
' set grouping of pvtDelays table
'
    worksheetA.Activate
    Range("C13").Select
    Selection.Group Start:=True, End:=True, By:=worksheetB.Range
("groups").value
    worksheetB.Select
    ActiveSheet.ChartObjects(1).Activate
    ActiveChart.PivotLayout.PivotTable.RefreshTable
End Sub

Cheers Michael
0
emte69 (54)
2/15/2009 1:57:40 PM
On Feb 15, 5:29 am, Shane Devenshire
<ShaneDevensh...@discussions.microsoft.com> wrote:
> --
> If this helps, please click the Yes button
>
> Cheers,
> Shane Devenshire
>
> "Michael.Tarnowski" wrote:
> > On Feb 15, 1:19 am, Shane Devenshire
> > <ShaneDevensh...@discussions.microsoft.com> wrote:
> > > Hi,
>
> > > I see the picture, but I don't see 2, 4, 6, or 5 as option or values
> > > anywhere in the picture.  So based on the picture, give me an example of what
> > > you want to have happen.  I see the "before"  what will it look like "after"?
>
> > > Also, before I go too much further, keep in mind that the pivot table will
> > > not change just by the user entering a number in a cell.  For that to happen
> > > you will need to add VBA code.  Second, if the process, whatever it may be,
> > > turns out to be fairly simple, I think you should teach the users how to do
> > > it.  One of the problems with programming is that when you leave, if no one
> > > else is a programmer and something goes wrong - nobody has a clue.
>
> > > --
> > > If this helps, please click the Yes button
>
> > > Cheers,
> > > Shane Devenshire
>
> > > "Michael.Tarnowski" wrote:
> > > > On Feb 13, 10:24 pm, Shane Devenshire
> > > > <ShaneDevensh...@discussions.microsoft.com> wrote:
> > > > > Hi,
>
> > > > > What kind of groupings are we talking about when we say 2, 4, 6, 5?
>
> > > > > What is being grouped and how?  What fields, row fields, more than one row
> > > > > field, column fields, both row and column fields?  Are these fields groups on
> > > > > Month, Day (if so how many), Year, Hour, Minute, Second, Numbers?  Or are
> > > > > these manual groupings?  Are these groupings those generated by the Grouping
> > > > > command or by anding other row or column fields, or by checking and
> > > > > unchecking filter options?
>
> > > > > --
> > > > > If this helps, please click the Yes button
>
> > > > > Cheers,
> > > > > Shane Devenshire
>
> > > > > "Michael.Tarnowski" wrote:
> > > > > > Hi experts,
>
> > > > > > I want to enable users with few knowledge of pivot techniques to
> > > > > > change the grouping of a pivot chart resp. the underlying pivot table.
> > > > > > The idea is, to have a changeable cell value beneath the chart to
> > > > > > enter the group (groups of 2, 4, 6, 5, etc...). The associated pivot
> > > > > > table shall change it's groupings accordingly, thus forces to change
> > > > > > the associated chart.
>
> > > > > > Any idea, how to achieve this?
> > > > > > Thanks and have a nice day
> > > > > > Michael
>
> > > > Hi Shane,
> > > > thanks for your answer.
> > > > It is a manually grouping. Here is a picture:
> > > >http://michaeltarnowski.de/temp/Clipboard02.gif
>
> > > > Michael
>
> > Shane,
> > the grouping happens in the field "weeks open". As you see, "weeks
> > open" is currently grouped by 5 (manual click in pivot table, then a
> > right click, "Group and Show Detail>Group").
> > I'am looking for a VBA code to change this grouping by changing a cell
> > value outside the pivot table only  -   thus, no right click in the
> > table, no more menu selection of "Group and Show Detail>Group", etc.,
> > -- only entering a value.
> > Michael

Hi community,
I found a solution:

if worksheet A holds the pivot table, and worksheetB the chart and a
cell names "groups" and C13 is a cell in the pivot table.

Sub PivotGrouping()
' set grouping of pvtDelays table
'
    worksheetA.Activate
    Range("C13").Select
    Selection.Group Start:=True, End:=True, By:=worksheetB.Range
("groups").value
    worksheetB.Select
    ActiveSheet.ChartObjects(1).Activate
    ActiveChart.PivotLayout.PivotTable.RefreshTable
End Sub

Cheers Michael
0
emte69 (54)
2/15/2009 1:57:50 PM
On Feb 15, 5:29 am, Shane Devenshire
<ShaneDevensh...@discussions.microsoft.com> wrote:
> --
> If this helps, please click the Yes button
>
> Cheers,
> Shane Devenshire
>
> "Michael.Tarnowski" wrote:
> > On Feb 15, 1:19 am, Shane Devenshire
> > <ShaneDevensh...@discussions.microsoft.com> wrote:
> > > Hi,
>
> > > I see the picture, but I don't see 2, 4, 6, or 5 as option or values
> > > anywhere in the picture.  So based on the picture, give me an example of what
> > > you want to have happen.  I see the "before"  what will it look like "after"?
>
> > > Also, before I go too much further, keep in mind that the pivot table will
> > > not change just by the user entering a number in a cell.  For that to happen
> > > you will need to add VBA code.  Second, if the process, whatever it may be,
> > > turns out to be fairly simple, I think you should teach the users how to do
> > > it.  One of the problems with programming is that when you leave, if no one
> > > else is a programmer and something goes wrong - nobody has a clue.
>
> > > --
> > > If this helps, please click the Yes button
>
> > > Cheers,
> > > Shane Devenshire
>
> > > "Michael.Tarnowski" wrote:
> > > > On Feb 13, 10:24 pm, Shane Devenshire
> > > > <ShaneDevensh...@discussions.microsoft.com> wrote:
> > > > > Hi,
>
> > > > > What kind of groupings are we talking about when we say 2, 4, 6, 5?
>
> > > > > What is being grouped and how?  What fields, row fields, more than one row
> > > > > field, column fields, both row and column fields?  Are these fields groups on
> > > > > Month, Day (if so how many), Year, Hour, Minute, Second, Numbers?  Or are
> > > > > these manual groupings?  Are these groupings those generated by the Grouping
> > > > > command or by anding other row or column fields, or by checking and
> > > > > unchecking filter options?
>
> > > > > --
> > > > > If this helps, please click the Yes button
>
> > > > > Cheers,
> > > > > Shane Devenshire
>
> > > > > "Michael.Tarnowski" wrote:
> > > > > > Hi experts,
>
> > > > > > I want to enable users with few knowledge of pivot techniques to
> > > > > > change the grouping of a pivot chart resp. the underlying pivot table.
> > > > > > The idea is, to have a changeable cell value beneath the chart to
> > > > > > enter the group (groups of 2, 4, 6, 5, etc...). The associated pivot
> > > > > > table shall change it's groupings accordingly, thus forces to change
> > > > > > the associated chart.
>
> > > > > > Any idea, how to achieve this?
> > > > > > Thanks and have a nice day
> > > > > > Michael
>
> > > > Hi Shane,
> > > > thanks for your answer.
> > > > It is a manually grouping. Here is a picture:
> > > >http://michaeltarnowski.de/temp/Clipboard02.gif
>
> > > > Michael
>
> > Shane,
> > the grouping happens in the field "weeks open". As you see, "weeks
> > open" is currently grouped by 5 (manual click in pivot table, then a
> > right click, "Group and Show Detail>Group").
> > I'am looking for a VBA code to change this grouping by changing a cell
> > value outside the pivot table only  -   thus, no right click in the
> > table, no more menu selection of "Group and Show Detail>Group", etc.,
> > -- only entering a value.
> > Michael

Hi community,
I found a solution:

if worksheet A holds the pivot table, and worksheetB the chart and a
cell names "groups" and C13 is a cell in the pivot table.

Sub PivotGrouping()
' set grouping of pvtDelays table
'
    worksheetA.Activate
    Range("C13").Select
    Selection.Group Start:=True, End:=True, By:=worksheetB.Range
("groups").value
    worksheetB.Select
    ActiveSheet.ChartObjects(1).Activate
    ActiveChart.PivotLayout.PivotTable.RefreshTable
End Sub

Cheers Michael
0
emte69 (54)
2/15/2009 1:58:05 PM
Hi,

I just reread my previous post and see that my code was not sent.  Here is 
the solution I provided for you 

This code allows you to enter a value in H3 and have a pivottable group by 
numbers with a By value equal to H3.  E4 is the first data cell of the field 
you are grouping on.


Private Sub Worksheet_Change(ByVal Target As Range)
    Dim isect As Range
    On Error GoTo ErrorHandler
    Set isect = Application.Intersect(Target, Range("H3"))
    If Not isect Is Nothing Then
        If Target > 0 Then
            Application.EnableEvents = False
            Range("E4").Group Start:=True, End:=True, By:=Target
        End If
    End If
ErrorHandler:
    Application.EnableEvents = True
End Sub

-- 
If this helps, please click the Yes button

Cheers,
Shane Devenshire


"Michael.Tarnowski" wrote:

> On Feb 15, 5:29 am, Shane Devenshire
> <ShaneDevensh...@discussions.microsoft.com> wrote:
> > --
> > If this helps, please click the Yes button
> >
> > Cheers,
> > Shane Devenshire
> >
> > "Michael.Tarnowski" wrote:
> > > On Feb 15, 1:19 am, Shane Devenshire
> > > <ShaneDevensh...@discussions.microsoft.com> wrote:
> > > > Hi,
> >
> > > > I see the picture, but I don't see 2, 4, 6, or 5 as option or values
> > > > anywhere in the picture.  So based on the picture, give me an example of what
> > > > you want to have happen.  I see the "before"  what will it look like "after"?
> >
> > > > Also, before I go too much further, keep in mind that the pivot table will
> > > > not change just by the user entering a number in a cell.  For that to happen
> > > > you will need to add VBA code.  Second, if the process, whatever it may be,
> > > > turns out to be fairly simple, I think you should teach the users how to do
> > > > it.  One of the problems with programming is that when you leave, if no one
> > > > else is a programmer and something goes wrong - nobody has a clue.
> >
> > > > --
> > > > If this helps, please click the Yes button
> >
> > > > Cheers,
> > > > Shane Devenshire
> >
> > > > "Michael.Tarnowski" wrote:
> > > > > On Feb 13, 10:24 pm, Shane Devenshire
> > > > > <ShaneDevensh...@discussions.microsoft.com> wrote:
> > > > > > Hi,
> >
> > > > > > What kind of groupings are we talking about when we say 2, 4, 6, 5?
> >
> > > > > > What is being grouped and how?  What fields, row fields, more than one row
> > > > > > field, column fields, both row and column fields?  Are these fields groups on
> > > > > > Month, Day (if so how many), Year, Hour, Minute, Second, Numbers?  Or are
> > > > > > these manual groupings?  Are these groupings those generated by the Grouping
> > > > > > command or by anding other row or column fields, or by checking and
> > > > > > unchecking filter options?
> >
> > > > > > --
> > > > > > If this helps, please click the Yes button
> >
> > > > > > Cheers,
> > > > > > Shane Devenshire
> >
> > > > > > "Michael.Tarnowski" wrote:
> > > > > > > Hi experts,
> >
> > > > > > > I want to enable users with few knowledge of pivot techniques to
> > > > > > > change the grouping of a pivot chart resp. the underlying pivot table.
> > > > > > > The idea is, to have a changeable cell value beneath the chart to
> > > > > > > enter the group (groups of 2, 4, 6, 5, etc...). The associated pivot
> > > > > > > table shall change it's groupings accordingly, thus forces to change
> > > > > > > the associated chart.
> >
> > > > > > > Any idea, how to achieve this?
> > > > > > > Thanks and have a nice day
> > > > > > > Michael
> >
> > > > > Hi Shane,
> > > > > thanks for your answer.
> > > > > It is a manually grouping. Here is a picture:
> > > > >http://michaeltarnowski.de/temp/Clipboard02.gif
> >
> > > > > Michael
> >
> > > Shane,
> > > the grouping happens in the field "weeks open". As you see, "weeks
> > > open" is currently grouped by 5 (manual click in pivot table, then a
> > > right click, "Group and Show Detail>Group").
> > > I'am looking for a VBA code to change this grouping by changing a cell
> > > value outside the pivot table only  -   thus, no right click in the
> > > table, no more menu selection of "Group and Show Detail>Group", etc.,
> > > -- only entering a value.
> > > Michael
> 
> Hi community,
> I found a solution:
> 
> if worksheet A holds the pivot table, and worksheetB the chart and a
> cell names "groups" and C13 is a cell in the pivot table.
> 
> Sub PivotGrouping()
> ' set grouping of pvtDelays table
> '
>     worksheetA.Activate
>     Range("C13").Select
>     Selection.Group Start:=True, End:=True, By:=worksheetB.Range
> ("groups").value
>     worksheetB.Select
>     ActiveSheet.ChartObjects(1).Activate
>     ActiveChart.PivotLayout.PivotTable.RefreshTable
> End Sub
> 
> Cheers Michael
> 
0
2/17/2009 6:21:27 PM
On Feb 17, 7:21 pm, Shane Devenshire
<ShaneDevensh...@discussions.microsoft.com> wrote:
> Hi,
>
> I just reread my previous post and see that my code was not sent.  Here is
> the solution I provided for you
>
> This code allows you to enter a value in H3 and have a pivottable group by
> numbers with a By value equal to H3.  E4 is the first data cell of the field
> you are grouping on.
>
> Private Sub Worksheet_Change(ByVal Target As Range)
>     Dim isect As Range
>     On Error GoTo ErrorHandler
>     Set isect = Application.Intersect(Target, Range("H3"))
>     If Not isect Is Nothing Then
>         If Target > 0 Then
>             Application.EnableEvents = False
>             Range("E4").Group Start:=True, End:=True, By:=Target
>         End If
>     End If
> ErrorHandler:
>     Application.EnableEvents = True
> End Sub
>
> --
> If this helps, please click the Yes button
>
> Cheers,
> Shane Devenshire
>
> "Michael.Tarnowski" wrote:
> > On Feb 15, 5:29 am, Shane Devenshire
> > <ShaneDevensh...@discussions.microsoft.com> wrote:
> > > --
> > > If this helps, please click the Yes button
>
> > > Cheers,
> > > Shane Devenshire
>
> > > "Michael.Tarnowski" wrote:
> > > > On Feb 15, 1:19 am, Shane Devenshire
> > > > <ShaneDevensh...@discussions.microsoft.com> wrote:
> > > > > Hi,
>
> > > > > I see the picture, but I don't see 2, 4, 6, or 5 as option or values
> > > > > anywhere in the picture.  So based on the picture, give me an example of what
> > > > > you want to have happen.  I see the "before"  what will it look like "after"?
>
> > > > > Also, before I go too much further, keep in mind that the pivot table will
> > > > > not change just by the user entering a number in a cell.  For that to happen
> > > > > you will need to add VBA code.  Second, if the process, whatever it may be,
> > > > > turns out to be fairly simple, I think you should teach the users how to do
> > > > > it.  One of the problems with programming is that when you leave, if no one
> > > > > else is a programmer and something goes wrong - nobody has a clue.
>
> > > > > --
> > > > > If this helps, please click the Yes button
>
> > > > > Cheers,
> > > > > Shane Devenshire
>
> > > > > "Michael.Tarnowski" wrote:
> > > > > > On Feb 13, 10:24 pm, Shane Devenshire
> > > > > > <ShaneDevensh...@discussions.microsoft.com> wrote:
> > > > > > > Hi,
>
> > > > > > > What kind of groupings are we talking about when we say 2, 4, 6, 5?
>
> > > > > > > What is being grouped and how?  What fields, row fields, more than one row
> > > > > > > field, column fields, both row and column fields?  Are these fields groups on
> > > > > > > Month, Day (if so how many), Year, Hour, Minute, Second, Numbers?  Or are
> > > > > > > these manual groupings?  Are these groupings those generated by the Grouping
> > > > > > > command or by anding other row or column fields, or by checking and
> > > > > > > unchecking filter options?
>
> > > > > > > --
> > > > > > > If this helps, please click the Yes button
>
> > > > > > > Cheers,
> > > > > > > Shane Devenshire
>
> > > > > > > "Michael.Tarnowski" wrote:
> > > > > > > > Hi experts,
>
> > > > > > > > I want to enable users with few knowledge of pivot techniques to
> > > > > > > > change the grouping of a pivot chart resp. the underlying pivot table.
> > > > > > > > The idea is, to have a changeable cell value beneath the chart to
> > > > > > > > enter the group (groups of 2, 4, 6, 5, etc...). The associated pivot
> > > > > > > > table shall change it's groupings accordingly, thus forces to change
> > > > > > > > the associated chart.
>
> > > > > > > > Any idea, how to achieve this?
> > > > > > > > Thanks and have a nice day
> > > > > > > > Michael
>
> > > > > > Hi Shane,
> > > > > > thanks for your answer.
> > > > > > It is a manually grouping. Here is a picture:
> > > > > >http://michaeltarnowski.de/temp/Clipboard02.gif
>
> > > > > > Michael
>
> > > > Shane,
> > > > the grouping happens in the field "weeks open". As you see, "weeks
> > > > open" is currently grouped by 5 (manual click in pivot table, then a
> > > > right click, "Group and Show Detail>Group").
> > > > I'am looking for a VBA code to change this grouping by changing a cell
> > > > value outside the pivot table only  -   thus, no right click in the
> > > > table, no more menu selection of "Group and Show Detail>Group", etc.,
> > > > -- only entering a value.
> > > > Michael
>
> > Hi community,
> > I found a solution:
>
> > if worksheet A holds the pivot table, and worksheetB the chart and a
> > cell names "groups" and C13 is a cell in the pivot table.
>
> > Sub PivotGrouping()
> > ' set grouping of pvtDelays table
> > '
> >     worksheetA.Activate
> >     Range("C13").Select
> >     Selection.Group Start:=True, End:=True, By:=worksheetB.Range
> > ("groups").value
> >     worksheetB.Select
> >     ActiveSheet.ChartObjects(1).Activate
> >     ActiveChart.PivotLayout.PivotTable.RefreshTable
> > End Sub
>
> > Cheers Michael

Hi Shane,
thank you to share your code with me. I assume both cells H3 and E4
are on the same worksheet. - I was looking for a code to handle H3 on
sheet A and E4 on sheet B and came up with my snippet.
Michael
0
emte69 (54)
2/18/2009 8:37:41 AM
Reply:

Similar Artilces:

Highlighted word wont DEL when I use Bakspc only in office/word
When I highlight a word in and hit the Backspace key it will not delete the word, this is only in outlook and word , however I can delete using the backspace key using one key stroke at a time. In Tools > Options > Edit, check "Typing replaces selection". (In Word 2007 this option is at Office button > Word Options > Advanced.) -- Regards, Jay Freedman Microsoft Word MVP FAQ: http://word.mvps.org Email cannot be acknowledged; please post all follow-ups to the newsgroup so all may benefit. backspace key not working correctly wrote: > When I ...

Formula without using numbers after decimal in the answer
I have a formula that derives the answer from a figure with a decimal. I don't want to use the figures after the decimal. Is there a way to just use the whole number and omit the numbers after the decimal without having to manually key in all these numbers manually? Thanks, Mustang You can use the INT function. This 'rounds down' any number to th nearest integer, e.g. if A1=2.567, a formula in B2 of =INT(A1) return 2 HTH Bruc -- swatsp0 ----------------------------------------------------------------------- swatsp0p's Profile: http://www.excelforum.com/member.php?...

Using Relative path for XML data file?
Is there a way to specify a relative path to an XML data file imported into Excel 2003? I am writing a web app that generates report data as XML for the user to download to their local machine. This data is to be consumed by an Excel reporting spreadsheet, which contains display-formatted tables and charts that are mapped to various data fields in an XML Map, which is in turn linked to the xml data file they will download. The idea is the user only needs to download the data for updates, not the whole spreadsheet. However, since I cannot predict the path where the user will store their...

Changing Cells and entering data in them
Thanks for the help again. Big thanks to Steve you've got me this far. I went out and bought a book, but it's like reading a foreign language. I was informed today that I can't have message boxes come up. I need to have the code point at the cells and if they are blank turn which ever one is blank red or if both are then both turn red then pause for each cell to be filled in. Cell F14 "Last Name" then automatically go to Cell F16 "First Name" on tab or enter. Basically if Cell F22 or F23 has an X in it, Cells F14 an F16 turn red and cell F14 has the focus...

Show date, time & day of week in one cell
Can I show the date, time and day of week in one cell. I have: 09/03/07 8:30 AM in one cell using the format (Format/Cells/Custom): [$-409]mm/dd/yy h:mm AM/PM;@ Excel refuses to accept ddd for Mon or dddd Monday at the end of the format I want it to read: 09/03/07 8:30 AM Monday in 1 cell. I have Excel 2003. One way: mm/dd/yy h:mm AM/PM dddd;@ In article <Xns99B8A3CAF9130pencilunlistedcom@208.49.82.220>, Burp <burp@beep.comINVALID> wrote: > Can I show the date, time and day of week in one cell. > > I have: > 09/03/07 8:30 AM > in one cell using the for...

default values in a cell
Hello, can you help me please Cell B1 contains a complex mathematical formula which requires (in several places) a number from cell A1. Cell A1 can contain any integer number, but it is usually the same (30). I would like to be able to leave cell A1 empty, and only enter a number when it is not 30 , ie the default value of A1 is 30, unless specified. How do I go about this ? Should I look at conditional formatting, or put lots of IF functions into an already complex formula? Thank as always KK Use 2 cells Modify the complex formula to use B1 rather than A1 ( or any other un-u...

Disable Secure Sockets Layer on exchange server when using RPC over HTTP
Hi im trying to enable RPC over HTTP to enable users to establish contact to my Excahger server 2003 over the internet. Now, I dont want to use SSL (security not that important) and i am told by this article that i can disable SSL in windows registry. Quote: Note While RPC over HTTP does not require Secure Sockets Layer, you must modify the registry to enable RPC over HTTP if you do not want to use Secure Sockets Layer. Microsoft recommends that you enable and require Secure Sockets Layer for your RPC over HTTP communications. At this address: http://support.microsoft.com/?id=833401 But i ...

Change position ID in HR
We would like to change the position ID in human resources. Does anyone have a suggestion on this. You would need to do it behind the scenes using a tool like Query Analyzer. -- Charles Allen, MVP "KT" wrote: > We would like to change the position ID in human resources. Does anyone have > a suggestion on this. careful though when you change it on the background as you need to know all the tables that use this position ID or Position Code and change it there too otherwise all the link would be gone and you end up with orphan records that its just the same as creatin...

using the journal on outlook
Once I link an email to the journal, can I still find that email in my mail box? I seem to be able to get to it only via the journal. If this is the way it is supposed to be, how do I remove it from the journal and get it back into my mail box? Am I just missing something? -- thanks, Independent Are you linking to the item or putting a copy into the journal item? Also, has the item been archived or not? "Independent" <Independent@discussions.microsoft.com> wrote in message news:868279F2-53C8-403A-97F5-604CEECD873C@microsoft.com... > Once I link an email to the journ...

Users ability to Manage Groups
When a users goes to the properties of a distribution group in outlook, they are able to use the modify members option, however as far as i can tell they don't have permissions to do this in AD. Does anyone have any ideas as to how to narrow down what is allowing this or how to disallow this ability? Thanks Matthew Loraditch On Fri, 23 Sep 2005 09:07:16 -0400, "Matthew Loraditch" <mloradites AT Yahoo DOT Com> wrote: >When a users goes to the properties of a distribution group in outlook, they >are able to use the modify members option, however as far as i can...

Pivot Table Question #5
How do I make the row headers show up in front of each row on pivot table instead of just once on the first row of a section? Thanks Try this: Copy the pivot table Do a Paste Special > Values into another sheet Ensure that the top left cell is A1 Run the Sub FillBlanks() below (from MVP Debra D) Sub FillBlanks() 'by Debra Dalgleish 7-Dec-2001 'fill blanks cells with data from above Range("A1").CurrentRegion _ .SpecialCells(xlCellTypeBlanks) _ .FormulaR1C1 = "=R[-1]C" Range("A1").CurrentRegion.Copy Range("A1").PasteS...

Call & Place Graphic Based on Cell Value?
Is there any way to call & place a graphic image based upon a cell value? Maybe you can look at J.E. McGimpsey's page: http://www.mcgimpsey.com/excel/lookuppics.html documike wrote: > > Is there any way to call & place a graphic image based upon a cell value? -- Dave Peterson ...

How do you change the APR?
How do you change the APR in a Credit Card account? Like if you have to change it from 13.250% to 14.240% Depends on the version but (in M2007) go to the CC Account and click on Change Account Settings. Scroll down to Credit Information and click on Change Credit Details. If you want to change the Minimum payment rate/amount as well, click on the Minimum payment amount and a side window opens. -- Regards Bob Peel, Microsoft MVP - Money For UK tips & fixes see http://support.microsoft.com/default.aspx?scid=fh;EN-GB;mny. I do not respond to any emails that I have not specifically ...

Viewing group calendars in CRM
I was wondering if it is possible to view other employees' activities and appointments in MS CRM? I know this feature is available in Outlook 2002 and higher through group schedules but I have a client running Outlook 2000 wanting this cabability. No, you can't. not sure if next version will have it. there is an ISV, http://www.ssyh.com/products_ssyh_MSCRM_GrpCal.html that has developed a group calender. we have found this useful for those migrating from Goldmine. I have this feature implemmented. If you are interested, contact me. []'s Vin�cius Pitta Lima de Ara�jo &qu...

Object pivot point
Is there a way to change the point at which an object will rotate? Or will it always rotate about the center? For instance, I would like a square to rotate about each corner. Thanks, Dave B You really need to invest in a drawing program. Serif has a free offering, DrawPlus 4. If you want the full feature version 6 it is $10.00. I use CorelDraw, it is simply a matter of moving the pivot point to the corner. I can't be sure Serif has this feature. I do know Publisher does not. You can use ruler guides to place your object and use the "format autoshape" to control the amount ...

After editing example1.xls and click SAVE, the filename changes to AABBEE.xls
After editing example1.xls and click SAVE, the filename changes to AABBEE.xls. The original file still exist and has been updated but the 'funny' filename also have the same content, and editable. The example1.xls is stored in a server and accessed by many people within the company. Everyone accessing to the file will change the filename unknowingly after saving it. ...

Business Portal Development
All: I know how to use Rational XDE, VS and the BP SDK to create new entities in BP, but how does one go about chaning labels? For example, if I wanted to change two labels on a requisition mgt screen: Can I just crack open the ASPX page and change the caption property? Thanks, Dwight -- Hi, To edit the lables you can use front page server extensions 2003. "Dwight Specht" wrote: > All: > > I know how to use Rational XDE, VS and the BP SDK to create new entities in > BP, but how does one go about chaning labels? > > For example, if I wanted to ch...

Actions to groups of tasks
Is there any way, or am I missing the obvious, to deal with multiple tasks in ones queue or list. As an example, I have a new release of my product, 1 task is to prepare the packages and 1 task is to ship them. If I have 100 customers, can I mass mark the tasks as finished or will have to open 200 dialogs and mark completed when the job is done? The only thing that I seem to be able to do to multiple tasks is delete them? Anyone? Jim Ball jball@XXXaccu-med.com remove the XXX to send an email you may be able to do this with a sql data update or create a solution in code to do thi...

Table of Contents for each section
I have tried several times to create a TOC for each section of my document. They way it is now I can simply go to the Insert a TOC and it will insert e perfect TOC. All my text has been changed to the appropriate Level and I have no problem there. But I need to seperate the chapters to the start of their respective sections (I have next page breaks inserted at the start of every chapter). I have tried the bookmark method, no luck. I have tried other methods but I think my problem is that I have selected the appropriate fields and changed their levels appropriately. I have not mes...

Status in table PurchaseOrderEntryDetail
Hello, We're trying to populate serial # into the PurchaseOrderEntryDetail table and run across the column 'Status'. What are the possible values for Status column in this table? Most of the time we see status of '2'. What does that mean? Please help. Many Thanks & Best Regards, Nikki ...

Does Outlook use the DAV protocol?
I'm an Outlook Express user who wants to switch to Outlook. I received a notice from Microsoft that includes the following: "... as of June 30, 2008, Microsoft is disabling the DAV protocol and you will no longer be able to access your Hotmail Inbox via Outlook Express." Please tell me if this action by Microsoft will affect Outlook in the same manner, or am I free to make the switch. "BudV" <BudVitoff@(NO)att.(SPAM)net> wrote in message news:%230XUDi%23zIHA.2384@TK2MSFTNGP02.phx.gbl... > I'm an Outlook Express user who wants to switch to Outlook...

form fields not saving to table
I have created a database with a form and three of the fields are not saving the information to the table. I have checked the row and control sources and now at a loss and thoughts? As you scroll through existing records is the data of the table displayed? Post the SQL of the query or form source. -- Build a little, test a little. "Kim" wrote: > I have created a database with a form and three of the fields are not saving > the information to the table. I have checked the row and control sources and > now at a loss and thoughts? > > ...

Let me use the Line Color icon on charts
It would speed up a lot of my work if I could use the Line Color icon on Excel charts, the same way I am able to use the Fill Color and Font Color icons. However, when I highlight any chart object, like the Plot Area, Chart Area, or a Series, the Line Color icon is disabled. -- Stuart Bratesman, Jr., MPP Muskie School of Public Service Univ. of Southern Maine Portland, Maine ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If ...

How do I lock individuals cells within an Excel spreadsheet so th.
i am trying to lock cells that have formulas in them, but other cells in the spreadsheet need to be unlocked so the end users can enter information. Hi first select the cells for which you want to allow entries and goto 'Format - Cells - Protection' and uncheck 'Locked'. Now protect the sheet 'Tools - Protection' "ucastores" wrote: > i am trying to lock cells that have formulas in them, but other cells in the > spreadsheet need to be unlocked so the end users can enter information. ...

How can I change text to proper text in multiple cells.
I need to change names that are all in caps to proper case in 100 cells. If I click each one individually, it works, but I need to be able to perfomr this funcion automatically on all the cells. One other post said to be sure calc is set to automatic and mine is. Any instruction is most appreciated. Thank you. Insert a helper column to the right of the column with the names. Then use a formula like: =proper(a1) and drag down that column Then select that column edit|copy select the original range Edit|paste special|Values And then delete the helper column. bethye99 wrote: > > I...