Friday, February 18, 2005

Using Expandable Range names in Excel

There are several ways to create range names in Excel and they have multiple uses. Here I talk about how to use them in a Data | Validation | List option.

I like to use the Validation List option to create drop down boxes for selections. I like to use range names for the lists for the selections.
I have discovered that if you create a range name fixed, Eg =$A$1:$A$5 for five selections, that if you want to add another option then you either have to insert a row (or cell) in the middle of the selection or change the range name. This is of course annoying (especially if you forget to change the range) and its not always possible to insert a cell if that would affect other tables.
Another solution is to make the entire column the range name eg =$A:$A. The main problem with this is in lists created using the Data Validation becasue they contain the entire columns of options. (Lots of blank options that a user could select.)

Many years ago one of my readers Mark developed the concept of using the OFFSET function to create an ever expandable range name.

Use this formula in the range name creation box.
=OFFSET(sheet_name!$A$2,0,0,COUNTA(sheet_name!$A:$A)-1)

This formula assumes there is a header row (if not remove the -1 at the end) and that there is no other data in the column other than the list options. It counts the number of items in the column and subtracts 1 for the header row. It then uses this number to create a range offset from the starting cell (which could be a range name or cell reference) in this case $A$2 (assuming A1 is the header).

If this has confused the heck out of you and you wonder why on earth go to all this trouble then you obviously haven't discovered the power of drop down selections and range names. Give it a try.

Email me if you need help making this work in your applications.


Posted by: Jethro | 12:28 AM |