Building a Pivot Table where column headings will change

I have a spreadsheet with columns from A to AE
The last 12 columns all contain months from the month AFTER the current month TO 12 months away.
e.g. nowm, they will show DEC05, JAN06, FEB06 .....etc

The rows contain a numerical value which represents the number of hours put
The other two columns I am concerned about are C and D - column headings ALWAYS stay the same.

I need to create a pivot table automatically that will always use the cooumn headings from the worksheet (whatever they are)
They'll exist in the same place, but obviously each month the pivot table is created, the month headings will be different.

how can I do this?
Is it possible to create a pivot and point to a cell reference when telling it which columns to use??

thanks in advance...

Hi

Your column headings have to be contiguous. You cannot have for example,
C,D,H,I,J,K.
You cannot leave a gap.
The data can come from a named range, and that range can be dynamic and
calculate the last column to use and/or the last row to use.
If you are saying you only want columns Cd and D plus 12 columns of months
in the future, could you not re-arrange your data so that the new columns
are inserted next to column D?

Alternatively, have a second sheet where this is the case and the data is
copied from Nov 06 to column D, from Oct 06 to column E etc. and use this
sheet as the source for your Pivot Table.
--
Regards

Roger Govier


"matpj" <matpj.1y4gvy_1131359402.2819@excelforum-nospam.com> wrote in
message news:matpj.1y4gvy_1131359402.2819@excelforum-nospam.com...
>
> I have a spreadsheet with columns from A to AE
> The last 12 columns all contain months from the month AFTER the current
> month TO 12 months away.
> e.g. nowm, they will show DEC05, JAN06, FEB06 .....etc
>
> The rows contain a numerical value which represents the number of hours
> put
> The other two columns I am concerned about are C and D - column
> headings ALWAYS stay the same.
>
> I need to create a pivot table automatically that will always use the
> cooumn headings from the worksheet (whatever they are)
> They'll exist in the same place, but obviously each month the pivot
> table is created, the month headings will be different.
>
> how can I do this?
> Is it possible to create a pivot and point to a cell reference when
> telling it which columns to use??
>
> thanks in advance...
>
>
> --
> matpj
> ------------------------------------------------------------------------
> matpj's Profile:
> http://www.excelforum.com/member.php...o&userid=21076
> View this thread: http://www.excelforum.com/showthread...hreadid=482758
>

maybe try the sumproduct function. i have a dislike of pivot tables,
and try to replace them with sumproduct functions where possible.

hi, the range is continuous, but on the pivot table i'm only displaying certain columns.


I just need to know how to tell it to use a column heading's cell value, rather than have a value hardcoded like this:

ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
"TotalCombined!C1:C31").CreatePivotTable TableDestination:="", TableName:= _
"PivotTable3"
ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)
ActiveSheet.Cells(3, 1).Select
ActiveSheet.PivotTables("PivotTable3").SmallGrid = False
ActiveSheet.PivotTables("PivotTable3").AddFields RowFields:=Array( _
"Sheam Type", "Sheam Desc", "Data")
With ActiveSheet.PivotTables("PivotTable3").PivotFields("NOV05")
.Orientation = xlDataField
.Caption = "Sum of NOV05"
.Position = 1
.Function = xlSum
End With
With ActiveSheet.PivotTables("PivotTable3").PivotFields("DEC05")
.Orientation = xlDataField
.Caption = "Sum of DEC05"
.Position = 2
.Function = xlSum
End With
With ActiveSheet.PivotTables("PivotTable3").PivotFields("JAN06")
.Orientation = xlDataField
.Caption = "Sum of JAN06"
.Position = 3
.Function = xlSum
End With

basically, instead of having 'PivotFields("DEC05")' I want it to be 'PivotFields(<U1>)'

if that makes any sense. is it possible?

i've also got to define the range as a name, as the number of rows will change

if you try sumproduct (or even sumif if you only have one criteria) you
will be able to do this without code. is ther any reason you have to
use a pivot table?

i'll check out the sum product function - thanks for the suggestion.

doing it manually with a pivot table and some tweaking afterwards, is very easy and gives me the format that the 'table' has always been in (its for one of our directors, so i didn't really want to change the format.

its a shame this forum doesn't allow embedding of files, otherwise i'd show you what I have and what I need to acheive!!

i'm not sure SUMPRODUCT will solve my problem.

I do need a certain layout to the results - and also then add some data from another workbook to the top of it.
It requires grouping on two different levels and then summing the data in rows T to AE.

i'd really rather find out if its possible to specify a cell reference in the PivotTable VBA as mentioned above.

this is the line that is the problem:
With ActiveSheet.PivotTables("HighLevelFOB").PivotFields("T1")

I need to tell it to look at a named worksheet and take the contents of cell T1 to be the field.

one other idea is to build your pivot table as normal, then have
another table that uses the now function to define the last 12 month /
next 12 months and then use getpivot data function to get the relevant
months data out of the table. but i still think the sumproduct function
is the way to go. mail me your spreasheet and i'll try to put you in
the right direction.