Totals/Pivot Table

Posted by Brian S. on December 03, 2001 10:13 AM

I'm having some trouble with this and not sure if I am approaching this properly. I have a sheet that I am using autofilter on. I would like to total the columns at the bottom, but whenever I change an filter property my grand total disappears. I'm looking to have the total display for each filter value, ie grand total, then change to product 1, and show the new total at the bottom of the records.

I original set this up as a pivot table but I think my data may be setup incorrectly. I have product, then the months 1-12 as headings and then the data.

I want the pivot table to look the same (ie product, 1-12 then the data) but it will only let me place the info in the data section. So my pivot table is (product, then 1
2
3
4
.
12

Is there a way to switch this?

Thanks for the help!!
Brian

Posted by Mark W. on December 03, 2001 11:28 AM

On your AutoFilter-ed list use =SUBTOTAL(9, [range])
for your totals... leave a blank row between your
data list and these totals.

In order to use a PivotTable as you've described you
must re-organize your data list into 1st Normal Form
(1NF). Your month columns are configured as a repeating
group. To achieve 1NF you must remove all repeating
groups. This process isn't for the faint of heart,
but it goes something like this...

1. Add a new column and sequentially number your
data records (rows).
2. Make 11 more copies of these record numbers
and place them directly beneath the existing records
3. Sort by these record numbers so that all 12
of the 1s, 2s, etc. are grouped together.
4. Use the TRANSPOSE function to re-orient your
months (1-12) and data values are on each of the
newly added (and numbered) records.
5. Use Go To... Special Blanks to select all blank
cells, enter a reference to the cell above the
active cell (e.g., =A2), and enter using the
Control+Enter key combinations.
6. Copy/Paste Special Values to replace all formulas
with their resulting values.

Posted by Brian on December 03, 2001 11:42 AM

Posted by Brian on December 03, 2001 11:43 AM

Mark-

Thanks for your help!! Worked PERFECTLY!!!

Brian