+ Reply to Thread
Results 1 to 10 of 10

Pivot table doesn't update from external csv while data table does

  1. #1
    Registered User
    Join Date
    11-24-2014
    Location
    Berlin, Germany
    MS-Off Ver
    Office 2013
    Posts
    5

    Pivot table doesn't update from external csv while data table does

    Hi everyone,

    I set up an Excel 2013 spreadsheet consisting of a data table and a pivot table generated from the data table. I would like to have data manually updated from an external .csv file on a daily base. When I click Refresh or Refresh all data on the standard table is updated with new lines added at the bottom. However, the pivot table never gets updated but remains populated with data up to the time when it was generated only.

    Clicking Connections shows that the range of the data table is properly extended on any refresh. New lines are listed under Locations. Why doesn't the pivot table update?

    Spreadsheet and reference sheet are stored in the same location. I don't get an error message.

    Thanks for your suggestions in advance.
    Kind regards,
    Nick

  2. #2
    Forum Guru romperstomper's Avatar
    Join Date
    11-04-2008
    Location
    A1
    MS-Off Ver
    Most
    Posts
    12,302

    Re: Pivot table doesn't update from external csv while data table does

    What happens if you then right-click and refresh the pivot table? Anything?
    Remember what the dormouse said
    Feed your head

  3. #3
    Registered User
    Join Date
    11-24-2014
    Location
    Berlin, Germany
    MS-Off Ver
    Office 2013
    Posts
    5

    Re: Pivot table doesn't update from external csv while data table does

    No. No indication of anything.

  4. #4
    Forum Guru romperstomper's Avatar
    Join Date
    11-04-2008
    Location
    A1
    MS-Off Ver
    Most
    Posts
    12,302

    Re: Pivot table doesn't update from external csv while data table does

    Apologies, lost track of this.

    What is shown in the dialog for the source data of the pivot? The table name, or a static address?

  5. #5
    Registered User
    Join Date
    11-24-2014
    Location
    Berlin, Germany
    MS-Off Ver
    Office 2013
    Posts
    5

    Re: Pivot table doesn't update from external csv while data table does

    Workbook Connections shows the file name of the source sheet (.csv). Connection Properties -> Definition shows the path to the source sheet.

  6. #6
    Forum Guru romperstomper's Avatar
    Join Date
    11-04-2008
    Location
    A1
    MS-Off Ver
    Most
    Posts
    12,302

    Re: Pivot table doesn't update from external csv while data table does

    That's not what I asked. What is the data source range for the pivot, not the connection for your query. (incidentally you can pivot the csv directly)

  7. #7
    Registered User
    Join Date
    11-24-2014
    Location
    Berlin, Germany
    MS-Off Ver
    Office 2013
    Posts
    5

    Re: Pivot table doesn't update from external csv while data table does

    The data source is Sheet1!$A$2:$K$452
    Table name and range are correct. Sheet1 is the table on the target sheet which gets populated (and new lines appended) any time I refresh. The pivot table on the same sheet remains showing data up to the time the pivot table was generated.

  8. #8
    Forum Guru romperstomper's Avatar
    Join Date
    11-04-2008
    Location
    A1
    MS-Off Ver
    Most
    Posts
    12,302

    Re: Pivot table doesn't update from external csv while data table does

    A static range like that won't update. You need to use the name of the table that is linked to your raw data.

  9. #9
    Registered User
    Join Date
    11-24-2014
    Location
    Berlin, Germany
    MS-Off Ver
    Office 2013
    Posts
    5

    Re: Pivot table doesn't update from external csv while data table does

    Thanks a lot. That was the hint I needed. I still don't get exactly the way it works. I used the Recommended Pivot Table option to generate a new table. It didn't update, either, until I noticed Change Data Source from the PIVOTTABLE TOOLS -> ANALYZE menu. I manually extended the data range to a cell way beyond the current bottom row. I had tried to do so before but it didn't work at all. Now the data is being updated in the pivot table, too. However, data source still is Sheet1!$A$2:$I$999. How can I actually make the range a dynamic reference?

    Thanks again & kind regards
    Nick

  10. #10
    Forum Guru romperstomper's Avatar
    Join Date
    11-04-2008
    Location
    A1
    MS-Off Ver
    Most
    Posts
    12,302

    Re: Pivot table doesn't update from external csv while data table does

    You need to use the name of the table on your data sheet - e.g. Table1

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 6
    Last Post: 02-23-2024, 02:11 PM
  2. Replies: 6
    Last Post: 07-31-2014, 12:56 PM
  3. Replies: 3
    Last Post: 03-28-2014, 12:04 PM
  4. Continues data table update from external sources
    By ajocius1 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 04-23-2009, 08:41 AM
  5. pivot table doesn't update on sheet active
    By carsto in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 11-29-2006, 09:46 PM

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1