Pivot tables and calculations

[Guest]

I have aged over the last few hours trying to tackle this myself..I am now
ready to say "uncle".

I have a sheet with data as follows for column headers:
style#
Units
fabric (jersey or rib or spandex, etc.)
fabric type (solid, printed, dyed, etc.)
color -body
lbs/doz -body
color-combo
lbs/doz - combo
trim color
lbs/doz - trim color
etc.

I am trying to make a pivot table that will include a calculation of the
above such as:
aqua * 4.1 lb/doz -body
white * .80 lb/doz - combo
yellow * .20/lb/doz - trim

And, to sort the table by fabric so I could end up with:
ALL the jersey fabric that is solid for example, grouped, for a total of
lbs/doz BY COLOR. Is this do-able? Should I not use a pivot table for this
type of report?

If it is do-able, can someone please tell me:
1. how can I get the fields to calculate..do I have to do the calcs on the
source data worksheet first?
2. how can I set up the pivot table to group as described?

Any advice would be most welcome!!

 

[dave]

A little hard to answer without much info but Ill try.

Im not sure what you mean by calculate - I don't see any
calculation going on - only summarizing the data.

If this is true, then it is certainly doable in the pivot
table. In general pivot tables will sort as categories
are placed in the left portion of the pivot table. So
using the wizard, you would place the first category that
you'd like to sort by on top, and add each additional
category below and (Im guessing from your message, but)
add the lbs per dozen is your data. I'd be glad to look
at your spreadsheet if you still have difficulty.

Dave

PS. If you are calculating the lbs / dozen, then it needs
to be done in the source data

 

[Guest]

I would be so grateful if you would take a look at the s/s. there is a macro
in it which you can disable; it's just for last saved time/date.

And, yes..I wanted to calculate the lb/dz in the pivot table but per your
reply I see it must be added to the source data first.

Please let me know where I can send the s/s.
Gratefully,
marcy

 

[dave]

my home email was on thr prior post - but if you need it
today you should now send it to (e-mail address removed)

 

[Guest]

The pivot table can calculate formula's when you choose:
from the pivot table toolbar:- Formula- Calculated field /
item.
DEpending how the source info is set up- one of the above
will work better than the other

David
Dublin

 

[Guest]

Sorry that I missed the email address; still don't see it. I see the one you
sent for today but now that the day is done, I guess I'll need the first one,
if you don't mind.

Anxiously awaiting your reply with thanks!