Pivot Tables and the OFFSET command

[gbarclay2001]

Hi all,

after looking at a web page Dave Hawley kindly directed me to after my last post (www.ozgrid.com/Excel/DynamicRanges.htm), I'm still unable to solve my problem.

I have a pivot table on which I need to use the OFFSET command to find the total(bottom) row of a column in the table. The amount of rows in the pivot table is changeable and that's why I can't set a static reference to it. Also, not all of the rows will contain data so I basically have to find the last cell containg a number in a column.

My last (poor) effort looks like this:

=OFFSET('By Value'!$C$5,0,0,,MATCH("*",'By Value'!$C:$C,0))

This is just giving me the value of C5 but the total (and last figure in the column) is cell C12.

Any ideas what I'm doing wrong?

Greg

[Mark W.]

Don't use OFFSET. Use GETPIVOTDATA instead. For example, given the following PivotTable whose upper-leftmost cell is H1...

Sum of Field3	Field2	*	*	*
Field1	Jan	Feb	Mar	Grand Total
Apple	10	20	15	45
Banana	34	*	12	46
Grand Total	44	20	27	91

...the formula, =GETPIVOTDATA(H1,"Grand Total"), will produce 91.

The formula, =GETPIVOTDATA(H1,"Mar"), will produce 27.

The formula, =GETPIVOTDATA(H1,"Apple"), will produce 45.

[ This Message was edited by: Mark W. on 2002-03-28 12:17 ]

[ This Message was edited by: Mark W. on 2002-03-28 15:38 ]