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.....
Posted by Mark W. on November 16, 2001 7:41 AM
Use the GETPIVOTDATA worksheet function (nt)
Posted by Sammy on November 16, 2001 9:57 AM
Re: Use the GETPIVOTDATA worksheet function (nt)
The GETPIVOTDATA worksheet function is wanting to look inside the PT. My data is outside the PT using vlookup's into the PT on Model number and pulling in data from other sources. Thanks though. Any other thoughts?
Posted by Mark W. on November 16, 2001 12:17 PM
Re: Use the GETPIVOTDATA worksheet function (nt)
Use GETPIVOTDATA to obtain the totals from your
PivotTable.
Posted by Sammy Groover on November 16, 2001 2:34 PM
Re: Use the GETPIVOTDATA worksheet function (nt)
Mark, thanks for your help. The trouble is not with the PT subtotals, it's with the other data outside the PT. Are you recommending that I turn off the PT subtotals and pull the whole spreadsheet data into another worksheet using the GETPIVOTDATA function? It only works on the PT correct?
Posted by Mark W. on November 16, 2001 3:28 PM
Re: Use the GETPIVOTDATA worksheet function (nt)
> GETPIVOTDATA... only works on the PT correct?
Yes.
Posted by Sammy on November 16, 2001 4:19 PM
Almost there I think..... please help someone!
Ok guys.... I can get a series of formulas to return the correct set of cells that I want to Subtotal. The cell contents looks like this:
L6:L22
When I put the Subtotal(9,my formulas) I get an error message. The Sum function returns the #Value! indicator. When I copy and paste special values from my cell and put the Subtotal function around it (ie. =Subtotal(9,L6:L22)) it works fine. Must be something in the translating the formulas. Is there a formula that you put around the formulas that converts the returned data (ie: L6:L22) into the Excel reference format?
Posted by Mark W. on November 16, 2001 4:25 PM
=SUBTOTAL(9,INDIRECT( [your formula] )) (nt)
