Hi,
Trying to find out if the following is possible, either via an Excel
setting that I can't find or via some VBA:

Assuming I have a speadsheet similar to this:

Salesman Year Month Dollars
John 2004 April 500.00
Paul 2004 February 250.00
George 2004 February 700.00
Ringo 2004 February 800.00
John 2003 December 500.00
Paul 2003 March 250.00
George 2003 November 700.00
Ringo 2003 October 800.00

Now I create a Pivot Table from this data, I drop 'Year' as my first page
field and 'Month' as the second.

The options for the 'Year' page field will be '2003' and '2004'.

If I select the 'Year' page field to be '2003', the options for the 'Month'
page field are April, February,
December, March, November & October.

Ideally I would like the 'Month' options to change to refelect the fact that
I have already applied 'filtering' via the
'Year' page field, so if I selected 2003 as the year then I would like the
'Month' page field to reflect
that selection. That is, only displaying December, March, Novemeber and
October for selection.

Is this possible or am I dreaming?

Cheers,


John.

The page fields aren't dependent, and there's no way to change that
behaviour. You could create a new column in your source table, and
concatenate the year and month. Add that field to the page area, an it
will be easier for users to determine which months are available for
each year.

JV wrote:
Hi,
Trying to find out if the following is possible, either via an Excel
setting that I can't find or via some VBA:

Assuming I have a speadsheet similar to this:

Salesman Year Month Dollars
John 2004 April 500.00
Paul 2004 February 250.00
George 2004 February 700.00
Ringo 2004 February 800.00
John 2003 December 500.00
Paul 2003 March 250.00
George 2003 November 700.00
Ringo 2003 October 800.00

Now I create a Pivot Table from this data, I drop 'Year' as my first page
field and 'Month' as the second.

The options for the 'Year' page field will be '2003' and '2004'.

If I select the 'Year' page field to be '2003', the options for the 'Month'
page field are April, February,
December, March, November & October.

Ideally I would like the 'Month' options to change to refelect the fact that
I have already applied 'filtering' via the
'Year' page field, so if I selected 2003 as the year then I would like the
'Month' page field to reflect
that selection. That is, only displaying December, March, Novemeber and
October for selection.

Is this possible or am I dreaming?

Cheers,


John.




--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html