ok, here's what i need to do:

I have an excel sheet with the following columns:

YEAR, PERIOD, REGION, CENTER, ME1, ME2, TOTAL, COLLECT

The year can be any year, period is 1 to 13, four regions (east, west,
south, midwest) and approximately 500 center. me1, me2, total, and collect
are numeric values.

I need to display the data like this:

Year Header: Year2005
Period Header: Period1
Region Header: Region1
Center1 me1 me2 total collect
Center2 me1 me2 total collect
Center3 me1 me2 total collect
Totals: me1 me2 total collect
Percent: me1 me2 total collect
Region Header: Region2
Center1 me1 me2 total collect
Center2 me1 me2 total collect
Totals: me1 me2 total collect
Percent: me1 me2 collect

this would repeat itself for each period based on the amount of centers in
that region. in example above it would be 3 centers in region1, and 2 in
region 2.
The totals row needs to total all of the centers in that region.
the percent row needs to take the percentage of total of me1 over total of
total, me2 over total and collect over total.

so sample data:
2005
Period 1
West
C4 100 150 250 50
C9 35 245 280 100
Totals: 135 395 530 150
Percent: 25 75 28

So I tried doing this in a pivot table but that does not allow you to put
the percentage row. I'm hoping there is a macro that i can write to just
format the actual data into the way i need it displayed with the percent.


"Dave Peterson" wrote:

Maybe you could record a macro when you format it the way you like.

Then you could run that macro to get that look when you want.

tkaplan wrote:

I am trying to create a pivot table based off of data in Sheet2. I need to
look to be different than any of the autoformats. is there a way that i can
create my own autoformat, maybe in VBA, that i can use for other tables as
well?

--

Dave Peterson