Results 1 to 8 of 8
  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Pivot Table not updating (2000)

    I have a pivot table that gets its data from an Access Query. I made changes to the data and went into the pivot table to refresh the data but when I put my cursor in a cell in the data to refresh, It was dimmed out. How do I get the data to refresh and change when the query that it is attached to changes?

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

    Re: Pivot Table not updating (2000)

    If you click in a cell inside the pivot table, you should always be able to update, unless you protected the worksheet, or shared the workbook, or something like that.

  3. #3
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Pivot Table not updating (2000)

    When I bring the data in, I give it a named range - I think that is where my problem is. I use =Offset('Sheet'!$A$1,0,0,CountA('Sheet1' !$A:$A),9) in the refers to property of the named range. I did this so that the range can shrink and grow with the data. I guess I messed up the link. If I just leave it the way it comes in, it works but when I refresh the data will it grow and shrink? I added this =Offset...... to the range that is created when the data is brought into Excel. When I add that =Offset......., the range goes away. I must be doing something wrong when I go into the insert | name etc. part.... I added a record and when I went in to refresh the data the range did not expand. If I change existing records, the refresh works. I haven't tested shrinking the data but I fear that doesn't work either.

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

    Re: Pivot Table not updating (2000)

    If I do it like that, I can update the pivot table, but - as I described earlier (last week?) - you have to update the data table first,, then the pivot table. If the update button is grayed out, there must be something wrong with the way the data have been imported. Perhaps best to try again.

  5. #5
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Pivot Table not updating (2000)

    I imported it several times. If I leave it the way it comes in, I can refresh but the only thing that gets refreshed are changes to the data. Added records do not get refreshed. I added a record and changed some records and refreshed the data. The changes were there but not the added record.

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

    Re: Pivot Table not updating (2000)

    I'm sorry, this is very difficult to analyze from a distance. Edits, additions and deletions should all be reflected in the imported data and in the pivot table - I just tested it again to make sure. Without seeing what you're actually doing I cannot tell where it goes wrong. If you wish, you can attach a zip file with a stripped down copy of the database and the workbook.

  7. #7
    Lounger
    Join Date
    Sep 2003
    Posts
    33
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Pivot Table not updating (2000)

    I am at home so I will try to explain what I am doing:
    I open Excel
    Click Data | Import External Data | New Database Query
    At next screen, I select MS Access Database*
    Click OK
    I go to where the database is and highlight it
    Click OK
    At the choose columns box
    I select the query and transfer the fields to the "Columns in your query" box
    I click next 3 times
    I Choose "Return data to Microsof Excel"
    Finish
    Where do you want to put data
    I choose existing worksheet
    OK

    Now data is there
    When I highlight the data, I see a name for the range called - Query_From_Ms_Access_database"

    Maybe here is where I go wrong
    While the range is highlighted
    I click Insert | Name |Define
    I click on the named range "Query_From_Ms_Access_Database"
    I put =Offset.....etc in the refers to box

    Then the named range isn't there and the refresh data is dimmed out.
    I know I am doing something wrong but I am not sure what.

    Thanks for any help.

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

    Re: Pivot Table not updating (2000)

    Ah - from one of your earlier threads I got the impression that Excel 2000 didn't create such a name, apparently I am mistaken.
    In fact, this makes it easier - you don't have to create a dynamic named range yourself - the name Query_From_Ms_Access_database created by Excel is already a dynamic range that will adapt itself automatically when the data are refreshed.
    So do NOT create a named range Query_From_Ms_Access_database yourself.
    You can use this name when creating the pivot table.

Posting Permissions

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