Pivot Tables and the OFFSET command
Amazing chart utilities from Jon Peltier
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 2 of 2

Thread: Pivot Tables and the OFFSET command

  1. #1
    New Member
    Join Date
    Mar 2002
    Location
    London
    Posts
    46
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    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

  2. #2
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

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








    Sum of Field3Field2
    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 ]

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

 

 
DMCA.com