Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 3 of 3

Thread: Pivot Table

  1. #1
    Board Regular
    Join Date
    Feb 2002
    Location
    Las Vegas Nevada USA
    Posts
    239
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Greetings from fabulous Las Vegas Nevada
    My project:
    A pivot table based on 6 columns of data (Month, name, region, credit card, amount, description).
    I have a procedure that creates it on button click and works fine.

    Problem:
    When new data is added to the bottom, the procedure must be ran again so the new data is included in the pivot table.
    All the resulting sheet numbers are getting large. Any way to prevent this?? I have reviewed Dave’s OZGRID site regarding dynamic named ranges but have not been able to implement the concept. Will this be able to solve the problem if indeed it is a problem? Is there a limit on how high a sheet number can go?

    Good on ya all

  2. #2
    MrExcel MVP Russell Hauf's Avatar
    Join Date
    Feb 2002
    Location
    Portland, OR Area - USA
    Posts
    1,607
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-03-28 06:40, GeorgeB wrote:
    Greetings from fabulous Las Vegas Nevada
    My project:
    A pivot table based on 6 columns of data (Month, name, region, credit card, amount, description).
    I have a procedure that creates it on button click and works fine.

    Problem:
    When new data is added to the bottom, the procedure must be ran again so the new data is included in the pivot table.
    All the resulting sheet numbers are getting large. Any way to prevent this?? I have reviewed Dave’s OZGRID site regarding dynamic named ranges but have not been able to implement the concept. Will this be able to solve the problem if indeed it is a problem? Is there a limit on how high a sheet number can go?

    Good on ya all
    Instead of defining specific rows with your columns, just use the entire columns as the range for your pivot table. For example, if your 6 columns are in columns A through F, then use $A:$F for your range. Then as you add data, you should be able to just refresh the pivot table to include the new data.

    Hope this helps,

    Russell

  3. #3
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-03-28 06:40, GeorgeB wrote:
    Greetings from fabulous Las Vegas Nevada
    My project:
    A pivot table based on 6 columns of data (Month, name, region, credit card, amount, description).
    I have a procedure that creates it on button click and works fine.

    Problem:
    When new data is added to the bottom, the procedure must be ran again so the new data is included in the pivot table.
    All the resulting sheet numbers are getting large. Any way to prevent this?? I have reviewed Dave’s OZGRID site regarding dynamic named ranges but have not been able to implement the concept. Will this be able to solve the problem if indeed it is a problem? Is there a limit on how high a sheet number can go?

    Good on ya all
    I'd recommend against using a column reference such as A:F because it will include empty rows up to 65,536 which will prevent you from Grouping date values.

    A dynamic range wouldn't be necessary if you just re-defined a named range (e.g., Database) to include newly added records, and then use that named range as the source of your PivotTable instead of using a cell range.

    The limit on the number of worksheets in a workbook is dependant upon available memory.

    [ This Message was edited by: Mark W. on 2002-03-28 10:57 ]

Some videos you may like

User Tag List

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
  •