filling in extra information for subtotals

  • Thread starter Thread starter aeg42
  • Start date Start date
A

aeg42

When I run subtotals on a set of data, it's obviously not that flexibl
in what it will display for the subtotals themselves. For instance,
have a three column worksheet where the first column has a product #
the second column has a description and the third column has
quantity. When I run the subtotals, I do a sum on the quantity at eac
change in product #. Pretty standard, right?
So the subtotals are there now, and on the left side of the screen, yo
have the collapsible tree for the subtotal function, so I can collaps
the spreadsheet to only display the subtotals. The problem with this i
that it only shows the product # and the subtotal for the quantity. Th
description basically disappears. Is there a way to get the descriptio
to appear on the same line as the subtotals?
As far as I know, you cannot get this to happen automatically
However, you can easily add them manually. For example, >suppose row
Click to expand...
is your first subtotal row and column B is your >description column. B
will be blank. You could put the formula >=B5 in here to replicate th
description. You can then copy this >cell into all the other subtota
rows, like this:
Copy cell B6.
Select cells in column B down to just before the grand total row
Then use Edit > Go To > Special > Blanks
This will select all the column B cells in subtotal rows, so Past
will now put the formula into them all.
Click to expand...

This helped immensely in terms of being able to do what I wanted wit
the subtotals, but what I ultimately need is to be able to automate th
process. Does anyone know of a way to get this to fill automaticall
the way I need it to?

Thanks,

Andre
 
AEG42,

Have a look at the pivottable.
Set it up so that both the product nr and the description are in the left
column
For 4 product that gave me this result (if it shwo a bit "normal" when
copied)

Sum of Quant
Nr Descr Total
1 Pr1 15
1 Total 15
2 Pr2 5
2 Total 5
3 Pr3 5
3 Total 5
4 Pr4 20
4 Total 20
Grand Total 45



--
Regards,
Auk Ales

* Please reply to this newsgroup only *
* I will not react on unsolicited e-mails *
 
Auk, I am somewhat familiar with Pivot Tables, but can you program
macro to create one? If so, do you know the code to set up th
different fields and totals
 
Aeg42,

I'm afraid that I've never before written a macro to create a tabel.
At the moment I'm to much lacking the time to sort everything out.

But if you are more or less familiar with Pivot tables, why don't you record
one and adapt the code to your needs.
That the way I would have to do it myself as well.

If you do need help, and if no one else can help you on this, I am willing
to do so in a couple of weeks, but at this moment I just can't make enough
time free for it.
As I said, i should have to sort out everything myself as well, cause I
don't do a lot with pivot tables so I certainly never had the need to
program one.

Sorry for the moment, but if needed you can email me (take the nospam out)
and as soon as possible I will try to assist you then.

--
Regards,
Auk Ales

* Please reply to this newsgroup only *
* I will not react on unsolicited e-mails *
 
Back
Top