Page 1 of 2 12 LastLast
Results 1 to 10 of 15

Thread: Pivot Table Data Source
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Feb 2006
    Posts
    150
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Pivot Table Data Source

    Hi

    I have an external data source in excel which I need a pivot table on, the data is growing and Im looking for a way of automatically changing (Expanding) the data source.

    The data comes from a web query and I am able to get the link from a right click on the data, but I cannot use this link as an external data source for the pivot table so it would appear I need a makro - I can find these to refresh data but not to expand the data selection.

    Thanks

  2. #2
    Board Regular sandy666's Avatar
    Join Date
    Oct 2015
    Posts
    2,299
    Post Thanks / Like
    Mentioned
    15 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Pivot Table Data Source

    Make source data as Table
    From default workbook use PowerQuery to "import" data from source file
    Create PivotTable with ExternalData (QueryTable)
    ---
    if source data will be updated, refresh PivotTable and you should see new data in the PT.
    it doesn't matter your source file will be open or closed
    I know you know but I forgot my Crystal Ball and don't know what you know



    In the first post, show the type of machine (PC / Mac) and the Office version you are working on
    impossible things we do on the spot. for miracles you need to wait for a while

  3. #3
    Board Regular
    Join Date
    Feb 2006
    Posts
    150
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Pivot Table Data Source

    Data comes from a cloud based finance package and the link to the data source (Right click on data then edit query) is longer than 256 characters so rejected when trying to set a pivot table to run from an external source..I also get a stack of script errors when editing the query..

  4. #4
    Board Regular sandy666's Avatar
    Join Date
    Oct 2015
    Posts
    2,299
    Post Thanks / Like
    Mentioned
    15 Post(s)
    Tagged
    1 Thread(s)

    Cool Re: Pivot Table Data Source

    External source for PivotTable means connection to the local QueryTable on your default workbook not to the cloud

    eg.

    Last edited by sandy666; May 16th, 2019 at 05:35 AM.
    I know you know but I forgot my Crystal Ball and don't know what you know



    In the first post, show the type of machine (PC / Mac) and the Office version you are working on
    impossible things we do on the spot. for miracles you need to wait for a while

  5. #5
    Board Regular
    Join Date
    Feb 2006
    Posts
    150
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Pivot Table Data Source

    Tried that, I get 'The type of connection selected cannot be used to create a pivot table'

  6. #6
    Board Regular sandy666's Avatar
    Join Date
    Oct 2015
    Posts
    2,299
    Post Thanks / Like
    Mentioned
    15 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Pivot Table Data Source

    could you post what are you doing from the beginning? step by step?
    I know you know but I forgot my Crystal Ball and don't know what you know



    In the first post, show the type of machine (PC / Mac) and the Office version you are working on
    impossible things we do on the spot. for miracles you need to wait for a while

  7. #7
    Board Regular
    Join Date
    Feb 2006
    Posts
    150
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Pivot Table Data Source

    Not sure myself The spreadsheet is created via our finance package and opens as a worksheet with an external data link - that link can only be amended using the finance package but changes made there filter through when the data is refreshed. As such no links are created directly in Excel and I don't think they can be, that's why I think I need to go down a makro route if possible?

  8. #8
    Board Regular sandy666's Avatar
    Join Date
    Oct 2015
    Posts
    2,299
    Post Thanks / Like
    Mentioned
    15 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Pivot Table Data Source

    did you try : Data - New Query - From Other Sources - From Web then paste link, OK etc....?
    I know you know but I forgot my Crystal Ball and don't know what you know



    In the first post, show the type of machine (PC / Mac) and the Office version you are working on
    impossible things we do on the spot. for miracles you need to wait for a while

  9. #9
    Board Regular
    Join Date
    Feb 2006
    Posts
    150
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Pivot Table Data Source

    More errors sadly

  10. #10
    Board Regular sandy666's Avatar
    Join Date
    Oct 2015
    Posts
    2,299
    Post Thanks / Like
    Mentioned
    15 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Pivot Table Data Source

    other way...
    Can you save this file (opened from the link) locally?
    I know you know but I forgot my Crystal Ball and don't know what you know



    In the first post, show the type of machine (PC / Mac) and the Office version you are working on
    impossible things we do on the spot. for miracles you need to wait for a while

Some videos you may like

User Tag List

Tags for this Thread

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
  •