Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Adaptable pivot tables
What is a general technique for handling the follow situation? I use pivot
tables and pivot charts to analyze a dataset. Each week, the dataset's record count increases (thus the rows the pivot table uses expands). I want to avoid manually re-building the pivot table and pivot charts each week and would like the pivot table's range to expand automatically to include all records. It appears using named ranges in the pivot table definition is not allowed. Also, I want to avoid using macros if possible. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Adaptable pivot tables
What if you just made your dataset range in the pivot table way greater than
the size of the actual data and then adjust the pivot table results to exclude the blank rows. For example, if your pivottable is for data on rows 1 - 1000, make the pivottable range refer to rows 1-50000. Then just suppress the viewing of the blank rows. "Allenx" wrote: What is a general technique for handling the follow situation? I use pivot tables and pivot charts to analyze a dataset. Each week, the dataset's record count increases (thus the rows the pivot table uses expands). I want to avoid manually re-building the pivot table and pivot charts each week and would like the pivot table's range to expand automatically to include all records. It appears using named ranges in the pivot table definition is not allowed. Also, I want to avoid using macros if possible. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Adaptable pivot tables
Personally I like dynamic named ranges. Name a Range via the Name
Manager (2007) or Insert - Name (pre 2007) In the Refers To box insert the following =OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),COUNT A(Sheet1!$1:$1)) This counts the number of values/text in the first column and the top row and expands or contracts with the size of your range. Reference your pivottable to this named range and all you will need to do is refresh it. Toy. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Building pivot tables in Excel 2007 based on existing pivot tables? | Excel Discussion (Misc queries) | |||
Adaptable pivot table ranges | Excel Programming | |||
Any way to programmatically make pivot tables behave more like data tables? | Excel Programming | |||
(Tom?) Pivot tables, code to refer to all pivot tables on template | Excel Programming | |||
Pivot tables-controlling user interaction with pivot tables | Excel Programming |