Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Pivot Table - Efficient Source Range
Hello,
I have 10 pivot tables that keying off the same range that has 63 columns and about 10,000 - 15,000 rows (fluctuates daily). What is more efficient for the source range in terms of recalculation (refreshing pivots): having dynamic named range or using some safe range like $A$1:$BK$16000? Thank you, -- ______ Regards, Greg |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Pivot Table - Efficient Source Range
Greg
I've not tested it but I would expect the $A$1 type addressing to be quicker as the range is just another layer and if it is using =OFFSET(...COUNTA(....)) type dynamic formula to find the end, it will be slower still. To speed it up you could have used the same PivotCache when building pivot tables on the same data (You should get a dialog to this effect as you build the tables) -- HTH Nick Hodge Microsoft MVP - Excel Southampton, England DTHIS web: www.nickhodge.co.uk UK Excel User Conference In Cambridge November 28th - December 1st 2007 Register Now @ http://www.exceluserconference.com/UKEUC.html "Greg" wrote in message ... Hello, I have 10 pivot tables that keying off the same range that has 63 columns and about 10,000 - 15,000 rows (fluctuates daily). What is more efficient for the source range in terms of recalculation (refreshing pivots): having dynamic named range or using some safe range like $A$1:$BK$16000? Thank you, -- ______ Regards, Greg |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Pivot Table - Efficient Source Range
Thank you, i will go w/ a reference range then
in fact, Debra Dalgleish has a nifty code for index cache consolidation. http://www.contextures.com/xlPivot11.html I was just trying to get any edge possible. -- ______ Regards, Greg "Nick Hodge" wrote: Greg I've not tested it but I would expect the $A$1 type addressing to be quicker as the range is just another layer and if it is using =OFFSET(...COUNTA(....)) type dynamic formula to find the end, it will be slower still. To speed it up you could have used the same PivotCache when building pivot tables on the same data (You should get a dialog to this effect as you build the tables) -- HTH Nick Hodge Microsoft MVP - Excel Southampton, England DTHIS web: www.nickhodge.co.uk UK Excel User Conference In Cambridge November 28th - December 1st 2007 Register Now @ http://www.exceluserconference.com/UKEUC.html "Greg" wrote in message ... Hello, I have 10 pivot tables that keying off the same range that has 63 columns and about 10,000 - 15,000 rows (fluctuates daily). What is more efficient for the source range in terms of recalculation (refreshing pivots): having dynamic named range or using some safe range like $A$1:$BK$16000? Thank you, -- ______ Regards, Greg |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Using cursor keys to select Pivot Table Source Data Range | Excel Discussion (Misc queries) | |||
Pivot Table Source Worksheet and Range | Excel Discussion (Misc queries) | |||
Increasing the Source Data range for an existing Pivot Table | Excel Worksheet Functions | |||
entry removed from source table remains in pivot table pull down | Excel Worksheet Functions | |||
Transforming pivot table back to the source table | Excel Worksheet Functions |