pivot table : formula to absolute reference a subtotal

[fax]

hi all.
ive been an avid reader of the forums for some time now; time to
register so i can post my question, which is rather urgent. (aren't
they all ?)

the attachment is a simplified version of the pivot table i have going
on.

the sourcedata is set so the production amount is reported under
'manufacturing', although there are costs in other types. i want to
have a 3rd column that would, for each product, do cost of that type /
production amount subtotal at each row. so it would basically be cost
breakdown at each row, with the total cost at the subtotal level.

i cant link to that specific or subtotal production amount, so i always
get 0 cost/production where production amount is 0.

if i were to manipulate the data so that production amount was
artificially distributed across all types, then the subtotal cost would
be wrong.

i tried with calculated formula/item to no avail. i have explored the
feasibility of trying to reference to the production amount data within
the calculated field. can't yet get it to work. help appreciated.

do i use consolidation here ?

thanks in advance for the help


+-------------------------------------------------------------------+
|Filename: ptformula.jpg |
|Download: http://www.excelforum.com/attachment.php?postid=4044 |
+-------------------------------------------------------------------+

 

[Suresh]

If my understanding of your problem is right - Try enabling Grand Total for
rows under table options which will give you a total of the production+cost
amounts for each type.

 

[fax]

pls check the attachment pic:
http://www.excelforum.com/attachment.php?attachmentid=4044&d=1132431812
.. the grand totals are already there.

what i want to do is to have cost / production amount for each cost
type. the problem is there is only 1 production amount, for other types
its 0. i need to always use that same production amount although vary
the cost. im looking to do almost an absolute referencing operation
within the pivottable. i suppose this has to be programmed with vba ?