Results 1 to 15 of 15
  1. #1
    Lounger
    Join Date
    Sep 2003
    Posts
    33
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Pivot Table (2000)

    I have a Pivot table that I have set up and called "Pivot table.xls".
    The data that I use for this Pivot table comes from a query that I create in Access.
    I want to export the results of this query to an excel file called "New data.xls".
    Then I want to copy the data from "New Data.xls" and open "Pivot Talbe.xls" and paste the new data over the previous data that I used to create the pivot table.
    Can I do this.
    The data will be the same except there may be more or less data.
    I know I have to refresh the data but do I have to do anything to the range to agree with the new data?
    I created this at work and to test it, I rerun the query so that I would have less data and copied the data to "New data.xls" and overwrote the data and tried to refresh and I got an error. I had to leave so I just didn't save "Pivot table.xls".
    I just wondered if I can overwrite the data in an already created Pivot table instead of redoing everything every time the data changes which will be mthly.

    I know I can create a pivot table using Access but that is not an option. They want to be able to click on a cell and see the underlying data so that is why I am exporting to Excel.

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

    Re: Pivot Table (2000)

    You can use Data | Get External Data | New Database Query to import data from an Access table or query into a worksheet. The imported range will automatically be assigned a name, probably Query_from_Access or something like that. This named range will expand or contract automatically when you refresh the data using Data | Refresh Data or the corresponding toolbar button.
    You can create a pivot table based on the named range. You can refresh the data table, then the pivot table.

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

    Re: Pivot Table (2000)

    Thanks Hans. So the Pivot table I already created should be recreated using what you suggested to get the data into the Excel spreadsheet. How do I refresh the data table?

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

    Re: Pivot Table (2000)

    First import the Access data using Data | Get External Data | New Database Query.
    You can then change the source of the existing pivot table. Click in any cell of the Pivot Table, then select Data | PivotTable and PivotChart Report... to start the Pivot Table Wizard. Click Previous to return to the step where you specify the source range. You can type the name of the imported data here. Click Finish.

    To refresh the data table, click anywhere in the imported data and select Data | Refresh Data.
    To refresh the pivot table after that, click anywhere in the pivot table and select Data | Refresh Data.

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

    Re: Pivot Table (2000)

    If I want to change the location of the database, how do I relink it to the Pivot table? Do I have to go through the whole process of Data, get External Data etc. I may have done this wrong but I selected a data source - Ms Access Database then I selected a database by browsing to it. When It created the worksheet with the data, it didn't give it a name. I did it by - Insert, Name, Define. I put this name in the range - = NewData. Did I do this right?

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

    Re: Pivot Table (2000)

    I'm using Excel 2002, perhaps adding a name to the imported data is a new feature there. Just assigning a name to the importing range won't work, it'll have to be a dynamic range. Say that you imported the data to a worksheet named MySheet, starting at A1, and that it has 7 columns. The "Refers To" property of the named range should be
    <code>
    =OFFSET('MySheet'!$A$1,0,0,COUNTA('MySheet'!$A:$A) ,7)
    </code>
    The use of COUNTA will let this range grow and shrink with the data.

    It is possible to change the source of imported data, see the thread starting at <post#=345360>post 345360</post#>.

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

    Re: Pivot Table (2000)

    In the Dialog box "Returning External data To Microsoft Excel", I chose a New Worksheet.
    There is a button in the dialog box for "Properties"
    I clicked on that
    There is a section called "If the number of rows changes upon refresh" with the following options:
    Insert cells for new data, delete unused cells
    INsert entire rows for new data, clear unused celss
    Overwrite existing cells with new data, clear unused cells

    I chose "Overwrite existing cells with new data, clear unused cells" - I wasn't sure between this one and the first one. Didn't see a difference
    Would this be this expand and shrink the data and since I used a named range called "AllData" would I still have to use the formula or would "AllData" be affected by this option?

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

    Re: Pivot Table (2000)

    When you refresh the imported data, rows in the worksheet will be inserted and deleted as needed.

    In general, Excel will automatically expand/contract a non-dynamic named range if rows inside the range are inserted/deleted, but not if rows are inserted below the current last one. So I still think a dynamic range would be best to guarantee that the pivot table will use the complete data.

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

    Re: Pivot Table (2000)

    I am trying to Edit External Data Edit query. I changed the External data to include a new field. That new field is not in the choices of data. Since I am getting the data from the query in Access, if I add a new field, how do I get it into the data in Excel?

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

    Re: Pivot Table (2000)

    See if you can edit the SQL for the query in MS Query, that should enable you to add the new field.

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

    Re: Pivot Table (2000)

    I am starting from scratch. I brought the data in from my Access query. I named a range called "Data". I am creating the pivot table. In the Range, I want to put the range named "Data" but it is not valid. I highlighted the data and then I did - Insert | Name | Define. I put "Data" for the name and I put in "Refers To"
    =Offset('Sheet1'!$A$!,0,0,CountA('Sheet1'!$A:$A),1 0). How do I refer to a named range when I setup my Pivot Table. I used =Data, I used Data by itself. What is the correct format?

  12. #12
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Pivot Table (2000)

    The refers to should be:
    <pre>=Offset('Sheet1'!$A$<font color=red>1</font color=red>,0,0,CountA('Sheet1'!$A:$A),10)</pre>


    Not:
    <pre>=Offset('Sheet1'!$A$<font color=red>!</font color=red>,0,0,CountA('Sheet1'!$A:$A),10)</pre>


    Where are you trying to enter the name? If I define a name called "Data" as the above, when I enter "=Data" or "Data" (no quotes) in the Pivot table wizard for the range, both work fine in XL2002. I can not test earlier versions at this moment, but I recall it working in XL97 this way so would suspect that it should in XL2000.

    Steve

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

    Re: Pivot Table (2000)

    Sorry - I just typed it wrong. Maybe I am not defining the area correctly. When I highlight the data, it doesn't show my named range. It gives me the following error - The pivottable field name is not valid
    To create a pivottable, you musgt use data that is organized as a list with labeled columns.......etc
    I am highlighting the data and define a named range. What am I doing wrong.

  14. #14
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Pivot Table (2000)

    In the insert name dialog box if you select the refers to, it should highlight the range on the sheet. If it does not highlight what you expect, the name is not created properly.

    You don't need to highlight the data, the "refers to" formula with offset is choosing the range.

    Steve

  15. #15
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Pivot Table (2000)

    Could you attach a "sample" worksheet with a range named as you have done it. The data can be completely "bogus" if desired.

    Steve

Posting Permissions

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