Excel Tip: Dynamic Named Ranges

Named ranges are a great tool in Excel. It helps writing and reading formulas faster, make less range-related errors and if you are not using it in complex calculations, you should. Directions on defining and using them you could find here. The functionality is simple but you should pay attention to the names you assigned to the ranges - you want name to be meaningful and unique. Otherwise you would waste time checking what was behind that name and reduce its benefits.

This is simple stuff and more interesting it gets with dynamic named ranges. These come of use when you need to refer to a range that changes under some conditions, e.g. list of sub-segments on selection of a segment or cities in a state, etc. You could create this type of range by using the function OFFSET - go to Name Manager and type it in the "Refers to" box. of course, you will need some helper dynamic values for position, number or rows and columns somewhere in the workbook. Refer to the range as a normal named range. In this way you could create named range with varying top-left position, number of rows and columns.

Another way to create dynamic named range in some cases is with INDEX function. As you may remember it returns the whole column from the referred range if you omit row parameter in it, e.g INDEX(Range, , 2) will return the whole second column in Range. Same with rows - returns row if column parameter is omitted, e.g INDEX(Range,2 ,) returns the second row in the Range. It is applicable only when named range has to include a row or a column from a range and top-left position does not change. There is a way to do that but it is too complicated and not very useful.

Both methods work well but keep in mind that their shortcomings:
- OFFSET is a volatile function and is recalculated every time something is changed in the workbook. This is particularly annoying and performance-killer with larger spreadsheets. Also, the function sometimes causes Excel to crash - I have not invested time to find exactly when this happens but in general this is a problem of larger workbooks.
- INDEX is not volatile but causes performance problems when a very large ranges are included in it. It is not applicable for variable position and size of the range but it is the better approach for the common case of dynamic named range including just a row or a column from a range.

Dynamic named range is a great tool for spreadsheets that require user interaction and their careful use opens possibilities for better and more flexible spreadsheets. If you find it interesting, dig further - there are lots of resources on the web to perfect this technique.