Results 1 to 8 of 8
  1. #1
    New Lounger
    Join Date
    Sep 2002
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Named Pivot tables (Excel 97)

    Is there any way to apply a name to a pivot table and subsequently have this named range available as you would a regular named/defined range, ie by pressing F3? The various pivot tables I use will rarely stay constant in range so re-referencing formulae or repetitively editing named ranges seems to be my only soluton. <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Named Pivot tables (Excel 97)

    You can name them by right clicking on the table (options). The name however does not show up in the Named ranges (so does NOT show up with F3), since it is not a named range but a named pivot table.

    Steve

  3. #3
    New Lounger
    Join Date
    Sep 2002
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Named Pivot tables (Excel 97)

    Thanks Steve! I take it there is no functionality to compensate for this either?

  4. #4
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Named Pivot tables (Excel 97)

    I guess what you need is a range name that adjusts itself, right?


    You could define a dynamic named range that adjusts itself *automatically* to the content. Assuming you have no blanks in either column A or row 1, create this named formula:

    - Insert, Name, Define
    - enter MyDynamicRange in the name box (or anything else)
    - enter this into the "refers to" box:

    =OFFSET($A$1,1,0,COUNTA($A:$A),COUNTA($1:$1))

    If your number of columns is fixed, exchange the COUNTA($1:$1) with a fixed number
    If your number of rows is fixed, exchange the COUNTA($A:$A) with a fixed number

    Try changing the dimensions of the table and press F5 (goto), entering MyDynamicRange as the destination.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  5. #5
    New Lounger
    Join Date
    Sep 2002
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Named Pivot tables (Excel 97)

    Much appreciated Jan, worked great!

  6. #6
    New Lounger
    Join Date
    Sep 2002
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Named Pivot tables (Excel 97)

    Jan,
    Really sorry to bother you with this again, but I have tried the same appraoch with another sheet and I'm finding the range is not including the last 14 rows of my table (albeit it is not looking at pivot table any longer!) . A1 is only a field name but A2:A15 has no data, the A16:A45 is populated with details i use for a vlookup.

  7. #7
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Named Pivot tables (Excel 97)

    Your range is 14 rows short since the cells in A2:A15 are Blank so are not counted!
    Try Changing:
    =OFFSET($A$1,1,0,COUNTA($A:$A),COUNTA($1:$1))
    to:
    =OFFSET($A$15,1,0,COUNTA($A:$A),COUNTA($1:$1))

    Steve

  8. #8
    New Lounger
    Join Date
    Sep 2002
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Named Pivot tables (Excel 97)

    Thanks Steve

Posting Permissions

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