Results 1 to 6 of 6
  1. #1
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Pivot Table Problem (Excel 2002)

    Hi

    I am struggling to extend the data range for a pivot table is there a way to do this without having to create a new table, I tried help but with no success.

    It does not appear to be picking up the last few rows.

    Many thanks

    Braddy
    If you are a fool at forty, you will always be a fool

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

    Re: Pivot Table Problem (Excel 2002)

    If you start the Pivot table wizard and click the Back button once, you should be able to redefine the data range.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  3. #3
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Pivot Table Problem (Excel 2002)

    Hi Hans

    That worked fine.

    Thanks

    Braddy
    If you are a fool at forty, you will always be a fool

  4. #4
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Pivot Table Problem (Excel 2002)

    Hi Jan Karel

    I sorry I thanked Hans in errror


    Braddy
    If you are a fool at forty, you will always be a fool

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Pivot Table Problem (Excel 2002)

    Method 1:
    - Click somewhere in the pivot table.
    - Select Data | PivotTable and PivotChart Report... (or click PivotTable in the Pivot Table toolbar and select Wizard from the popup menu)
    - Click the <Back button.
    - Select the extended range.
    - Click Finish.
    You will have to repeat this each time the source range expands or contracts.

    Method 2:
    - Create a dynamic named range:
    Say that the source range for the pivot table starts in A1 and has 5 columns.
    Select Insert | Name | Define...
    Enter a name, for example Source, in the Names in workbook box.
    Enter the following in the Refers to box:

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

    If you want the width to be dynamic too, use

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

    Click OK
    - Use the named range as data source for the pivot table:
    Click somewhere in the pivot table.
    Select Data | PivotTable and PivotChart Report...
    Click the <Back button.
    Enter the name you defined (Source in my example).
    Click Finish.
    When the source range expands or contracts, you only need to click the Refresh Data button on the Pivot Table toolbar.

  6. #6
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Pivot Table Problem (Excel 2002)

    Hi Hans

    This will be very useful, as I have to run a report every month based on this data which is downloaded from Business Objects, columns will not vary but rows will.

    Many thanks

    braddy
    If you are a fool at forty, you will always be a fool

Posting Permissions

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