Eric58132
08-24-2010, 10:10 AM
Hi All,
I have a file that is completely overloaded with "stuff", and it takes an exceptionally long time to recalculate. Because of this, I'm trying to optimize the speed of the file.
There are over a thousand calculations done involving sumproducts/sumifs/other arrays that use a range moving from A51:A65000 in my file (for example), and I am trying to optimize these calculations by implementing dynamic named ranges.
The site I found to help me in this is here:
http://www.ozgrid.com/Excel/DynamicRanges.htm
Within this page lies the formula (slightly modified) that I used for my ranges. It is =OFFSET($A$1,0,0,COUNTA($A:$A),1)
In this example with A51:A65000, I modified the formula to read
=OFFSET($A$51,0,0,COUNTA($A:$A),1)
....Now here is my problem. Is there a way to change the CountA() portion of the formula to not read the entire "A" column? Basically I'm looking for something like CountA($A$51:$A), but if I want to name the starting point of my range, Excel forces me to name the end as well, which contradicts the whole point of this exercise in the first place.
Can anyone help?
I have a file that is completely overloaded with "stuff", and it takes an exceptionally long time to recalculate. Because of this, I'm trying to optimize the speed of the file.
There are over a thousand calculations done involving sumproducts/sumifs/other arrays that use a range moving from A51:A65000 in my file (for example), and I am trying to optimize these calculations by implementing dynamic named ranges.
The site I found to help me in this is here:
http://www.ozgrid.com/Excel/DynamicRanges.htm
Within this page lies the formula (slightly modified) that I used for my ranges. It is =OFFSET($A$1,0,0,COUNTA($A:$A),1)
In this example with A51:A65000, I modified the formula to read
=OFFSET($A$51,0,0,COUNTA($A:$A),1)
....Now here is my problem. Is there a way to change the CountA() portion of the formula to not read the entire "A" column? Basically I'm looking for something like CountA($A$51:$A), but if I want to name the starting point of my range, Excel forces me to name the end as well, which contradicts the whole point of this exercise in the first place.
Can anyone help?