Subtotaling next to a pivot table using the pivot table subtotal breaks
Posted by Sammy on November 15, 2001 6:20 PM
I have a pivot table with subtotals in it. To the right of the PT, I have several columns of data from either a vlookup into another worksheet or from formulas. This data (outside the PT) does not have subtotals in it and needs to. I was trying to look at the far left cell (of the PT)and if it ends with "Total" (from the PT subtotal), then perform some type of formula to subtotal upward to the start of the category. Using the Offset and Match functions, I can idenify the cell above me that starts the category. By using the =CELL("address",OFFSET(L20,-(ROW()-MATCH(LEFT(A21,2),A:A,0)-1),0)) formula, it returns the $L$6 value to my cell. This is the first cell above me in the category I want to subtotal. I don't know how to use this info and do subtotals from L20 (directly above me) upward to L6 (derived from the formula). I can't imbed the above into the Subtotal function because it returns an error. Any ideas? I can't find how to put something like:
= subtotal(9,L20:my ref from my formula)without an error. Thanks guys.....