Results 1 to 7 of 7
  1. #1
    5 Star Lounger
    Join Date
    May 2001
    Location
    Philadelphia, Pennsylvania
    Posts
    676
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Getting Pivot talbe data (Excel 2003)

    I am creating a dashboard-type excel file for management. I want to show them a Pivot table or two based on data in another Excel file. I am trying to avoid placing the whole datafile in the report workbook by getting the PT source to reach into the source data workbook from the dashboard workbook. When, in the dashboard workbook, I start the PTWizard and select the Microsoft Excel file source and try to browse to it, the Range box shows the file name.xls! and says Reference is not Valid when I click Next. I have a tab DATA with a dynamic range name set up. First, can i do this? Second, I tried (a little) that External data source route with no success. Is that the way to do it? TYIA

  2. #2
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Getting Pivot talbe data (Excel 2003)

    Hi,
    When you click on the browse button, it adds the path to the source workbook in the textbox...you still need to type the range reference pointing to the cells that contain the list. Once you type the lists cell reference, you can choose next!
    Cheers
    Regards,
    Rudi

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

    Re: Getting Pivot talbe data (Excel 2003)

    Set the source to 'Other workbook.xls'!RangeName where Other workbook.xls is the name of the source workbook (which must be open) and RangeName is the name of the dynamic range.

  4. #4
    5 Star Lounger
    Join Date
    May 2001
    Location
    Philadelphia, Pennsylvania
    Posts
    676
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Getting Pivot talbe data (Excel 2003)

    Ok. It seems that the source workbook must be open. I was hoping that would not be necessary. Is this correct? Are there alternatives? I think sumproduct can reach into a closed workbook but PTs cannot?

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

    Re: Getting Pivot talbe data (Excel 2003)

    Since a pivot table is not updated automatically, you can close the source workbook after creating the pivot table.
    If the source range is fixed, you can even update the pivot table when the source workbook is closed.
    But if the source range is dynamic, the source workbook must be open when you update the pivot table - Excel cannot read a dynamic range in a closed workbook.

  6. #6
    5 Star Lounger
    Join Date
    May 2001
    Location
    Philadelphia, Pennsylvania
    Posts
    676
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Getting Pivot talbe data (Excel 2003)

    OK Thanks for the clarification

  7. #7
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    Vancouver, Br. Columbia, Canada
    Posts
    268
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Getting Pivot talbe data (Excel 2003)

    If you prefer not to have the data source open, can you include a "before close" routine that will rename the required named range to the same range that would have been calculated for the dynamic range? That way the dashboard file should be able to read the data for the Pivot Table even if the data file is closed.

Posting Permissions

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