locked
Pivot Table refresh with an external data range RRS feed

 > 

  • Question

  • Question
    Sign in to vote
    0
    Sign in to vote

    HI,

    I have a few sheets in an .xlsx with an external connection for each sheet to a SharePoint site. Once the .xlsx all the data gets refreshed from the SharePoint site.

    I have also created a Pivot table with data from one of these sheets. The issue I have is that the Pivot table doesn't update or refresh when I reopen the .xlsx file or when I click on refresh.

    If I try to create a table in one of the Sheets, I get : your selection overlaps one or more external data ranges. Do you want to convert the selection to a table and remove all external connections?

    Anyone have any ideas?

    Thanks

    Noel

    Wednesday, December 9, 2015 12:45 PM

All replies

  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi Noel,

    Based on your description, did you try to insert data from SharePoint site by Data-From Web?

    If yes, I can reproduce your problem and it seems that this is a by design behavior in Excel 2010.

    Since Format as a table will create a source and overlap the existing source, you could not format the web query.

    Another option is use the XML in the Developer Tab which you could import the xml from SharePoint directly as a XML source. The XML source will create a table and you don't need to format it again.

    Please try this method and check if it works for you.

    Any updates please let me know, I'm glad to help and follow up your reply.

    Regards,

    Emi Zhang
    TechNet Community Support

    Please mark the reply as an answer if you find it is helpful.

    If you have feedback for TechNet Support, contact tnmff@microsoft.com.
    Thursday, December 10, 2015 5:51 AM
  • Question
    Sign in to vote
    0
    Sign in to vote

    hi Emi,

    I have 3 .csv files saved on SharePoint and connect the 3 Sheets via one locally saved .xslx file. I thought I would be able to simply create a Pivot table from the Sheets and that it would refresh when I clicked on refresh but unfortunately it doesn't. Perhaps you have another idea how I could do this?

    Should I convert the .csv files to XML files?

    Thanks

    Noel

    Friday, December 11, 2015 8:01 AM