How to Copy Field Values to Group Subtotals row


G

Guest

I wasn't sure how to word the subject, but in short here's my situation:

I have a worksheet with hundreds of subtotals (that will grow in count
monthly). When I create a subtotal for a given value change, it enters a row
in and then enters a title for the given value change, but that's it (aside
from the totals).

I have 5 columns and to avoid manually updating the values myself, I want to
have them entered automagically :)

Example (copy table below in Excel to see current format):
Reg Ctry Grp Date Cust Class Prod Score Sales Amount
1 A A Jan-07 P NA NA 1000 100 15000
NA Total 1000 100 15000
1 A A Jan-07 P H Ac 1200 80 16000
H Total 1200 80 16000


Now copy the table below to see what I actually want:
Reg Ctry Grp Date Cust Class Prod Score Sales Amount
1 A A Jan-07 P NA NA 1000 100 15000
1 A A Jan-07 P NA Total 1000 100 15000
1 A A Jan-07 P H Ac 1200 80 16000
1 A A Jan-07 P H Total 1200 80 16000


Is there a magical way to have those values copied down? I need to enter the
data each month, sort it, and then generate the subtotals. When they are all
collapsed, I'd like to be able to see the Reg, Ctry, Grp, etc. for every
subtotal.

My thanks in advance.

Vexed in VanCity,
JCH
 
Ad

Advertisements

D

Dave Peterson

How about an alternative?

You can get the numeric summaries pretty quickly using Data|pivottable. I've
found that with lots of data, that pivottables are quicker than data|subtotals.

If you've never use pivottables, here are a few links:

Debra Dalgleish's pictures at Jon Peltier's site:
http://peltiertech.com/Excel/Pivots/pivottables.htm
And Debra's own site:
http://www.contextures.com/xlPivot01.html

John Walkenbach also has some at:
http://j-walk.com/ss/excel/files/general.htm
(look for Tony Gwynn's Hit Database)

Chip Pearson keeps Harald Staff's notes at:
http://www.cpearson.com/excel/pivots.htm

MS has some at (xl2000 and xl2002):
http://office.microsoft.com/downloads/2000/XCrtPiv.aspx
http://office.microsoft.com/assistance/2002/articles/xlconPT101.aspx
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top