Results 1 to 10 of 10
  1. #1
    New Lounger
    Join Date
    Sep 2001
    Location
    Paignton, Devon, England
    Posts
    21
    Thanks
    0
    Thanked 0 Times in 0 Posts

    dynamic named ranges in pivot tables (2000)

    Morning All,
    I've been trying to apply a dynamic named range to a pivot table using:
    =OFFSET($B$10,0,0,COUNTA($B$10:$B$65536),1)
    where B10 is the first cell in the row field under the label.
    Unfortunately, it doesn't seem to change the range when the number of rows in the table changes.
    I'd appreciate any thoughts on the subject cos its driving me mad!

  2. #2
    5 Star Lounger
    Join Date
    Dec 2000
    Location
    Reading/Swindon, Berkshire, United Kingdom
    Posts
    664
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: dynamic named ranges in pivot tables (2000)

    Your formula works for me.

    The only thing I can think of is: you haven't got any blank entries in column b within the table have you?

  3. #3
    New Lounger
    Join Date
    Sep 2001
    Location
    Paignton, Devon, England
    Posts
    21
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: dynamic named ranges in pivot tables (2000)

    No, no blank entries. It seems to work ok sometimes, but if the number of rows increases when I change the table the range size doesn't always increase to match. Deeply puzzling... The formula works perfectly outside pivot tables.

    Thanks

    Graeme

  4. #4
    5 Star Lounger
    Join Date
    Dec 2000
    Location
    Reading/Swindon, Berkshire, United Kingdom
    Posts
    664
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: dynamic named ranges in pivot tables (2000)

    >>>>>>>>if the number of rows increases when I change the table

    is this by refreshing the background data, moving data fields around or unhiding certain items within a data field?

  5. #5
    New Lounger
    Join Date
    Sep 2001
    Location
    Paignton, Devon, England
    Posts
    21
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: dynamic named ranges in pivot tables (2000)

    The table only changes when the user changes the page field using the dropdown; the page field is Week Number and the number of items in the row field changes from depending on which week the user selects. The background data doesn't change. Still baffled!

    Graeme

  6. #6
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: dynamic named ranges in pivot tables (2000)

    A pivot table will not automatically update when the th eunderlying data changes. You do need to refresh the pivot table after any data addition. You could of course have a worksheet change event carry out the refresh.

    Andrew C

  7. #7
    New Lounger
    Join Date
    Sep 2001
    Location
    Paignton, Devon, England
    Posts
    21
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: dynamic named ranges in pivot tables (2000)

    The underlying data doesn't change; both it and the pivot table are refreshed on file-open. All that changes is the user selects a different week from the Week Number pagefield drop-down.
    On a similar subject, when the table is changed the Worksheet_Change event doesn't fire; is this the way it should work or am I missing something?

  8. #8
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: dynamic named ranges in pivot tables (2000)

    < is this the way it should work >

    Yes, that is the way it should / does work. However the Worksheet_Calculate event should fire when you change the PT view using page fields etc or when you refresh even if the underlying data has not changed.

    Andrew

  9. #9
    5 Star Lounger
    Join Date
    Dec 2000
    Location
    Reading/Swindon, Berkshire, United Kingdom
    Posts
    664
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: dynamic named ranges in pivot tables (2000)

    I've got a pivot table running here to try and duplicate the problem, and I can't. When I change the week on display the range resizes. What are the chances of you posting a cut down version of the book?

    and yes, that is the way it works for me too - the change event doesn't fire.

  10. #10
    New Lounger
    Join Date
    Sep 2001
    Location
    Paignton, Devon, England
    Posts
    21
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: dynamic named ranges in pivot tables (2000)

    I would send the workbook, but it seems to be working fine now and I can't duplicate the problem either! Still don't understand what happened, but thank you both for your help. And thanks, Andrew, for the tip about the calculate event; exactly what I needed.

    Graeme

Posting Permissions

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