Pivot Table

[GeorgeB]

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

 

[Russell Hauf]

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

 

[Mark W.]

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