| When you often have to add extra rows or columns
of data to your spreadsheets, you need to find a way of having
your Excel formulae automatically refer to the new data area.
You can do this by using a large range in your formulae that
extends well beyond your current data boundaries, for instance
by referring to a whole column. However this causes very inefficient
calculation.
The solution is to use Dynamic Named Ranges. By using the OFFSET
and COUNTA functions in the definition of a named range, the
area that the named range refers to can be made to dynamically
expand and contract. For example create a defined name as:
=OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),1)
When you use the dynamic range name in a formula it automatically
expands to include new entries.
The benefit of this (apart from the fact that you don’t
have to change all your formulae) is that Excel then only has
to calculate using exactly the right number of rows and columns.
However there is a performance hit if you use a lot of Dynamic
Ranges.
The main performance hit is because OFFSET is a volatile function
and therefore is always recalculated, and because the COUNTA
function inside the OFFSET has to look at a large number of
rows. You can minimise this performance hit by storing the COUNTA
part of the formula in a separate cell, and then referring to
the cell in the dynamic range:
Counts!z1=COUNTA(Sheet1!$A:$A)
DynamicRange=OFFSET(Sheet1!$A$1,0,0,Counts!$Z$1,1)
You can also use functions like INDIRECT to construct dynamic
ranges.
The FastExcel sample problem
makes extensive use of dynamic range names, and also shows how
to optimise them. It also contains two versions of the optimised
workbook, one with dynamic names, and one without.
FastExcel Version 4 has a Dynamic Range Wizard that makes it
easy to create dynamic range names, and a several new counting
functions that overcome the limitations of the COUNTA function.
For further explanation of Dynamic Ranges see:
|
