Dynamic Pivot

[Shane]

Howdy,

I have a data source that adds a new month every month to extend the
forecast another month. I want the pivot to show only the most current
months. So I want to add the new month to the pivot and remove a
previous month.

Any suggestions would be appreciated.

 

[Jim Thomlinson]

I can understand your wanting to add the new month to your pivot and that is
fairly easy to accomplish with a dynamic named range. Check out this link...

http://www.contextures.com/xlPivot01.html

I am not too sure why you would be removing any history though. Pivot tables
are extremely efficient so there is no really compelling reason to remove any
of the history. All you really need to do is to filter out the old info that
you don't want to see which is pretty straight forward in a pivot table...

 

[Shane]

I can understand your wanting to add the new month to your pivot and thatis
fairly easy to accomplish with a dynamic named range. Check out this link....

http://www.contextures.com/xlPivot01.html

I am not too sure why you would be removing any history though. Pivot tables
are extremely efficient so there is no really compelling reason to removeany
of the history. All you really need to do is to filter out the old info that
you don't want to see which is pretty straight forward in a pivot table....

I should have clarified. I am looking to be able to dynamically change
the fields that show up in the pivot table. Thanks

 

[Jim Thomlinson]

What does your source data look like. Are you adding new fields each time a
new period is added or are you just adding a new member to an existing field?

 

[Shane]

What does your source data look like. Are you adding new fields each timea
new period is added or are you just adding a new member to an existing field?

I have a List where columns denote type as well as month with the data
under the months being dollars. So I would go in and select the
location where the expense will be incurred, the type of expense, and
then I choose the month in which the expense is made. I also have a
column that sums up the most recent twelve months.
If it helps here are my column headers. In this instance I would want
Aug 08 though Jul 09 on the pivot. The next month I would want Sept 08
to Aug 09.

Location Type BU Desc Equip Type Obj Desc Begin Net Value Asset Type
Months Jan-08 Feb-08 Mar-08 Apr-08 May-08 Jun-08 Jul-08 Aug-08 Sep-08
Oct-08 Nov-08 Dec-08 Jan-09 Feb-09 Mar-09 Apr-09 May-09 Jun-09 Jul-09
Twelve Month Total Total Comment