This is the second in a series of posts in which I will introduce you to the Excel concept of dynamic named ranges, which are a very useful piece of knowledge to have at your disposal. In this weeks post, how would you go about creating a dynamic named range, what scope is in a named range context, and some limitations that dynamic named ranges have.

How do I go about creating a dynamic named range?

Dynamic named ranges are generated using the Name Manager, or the Define Name button. Set the “Name” and “Scope” (see below) as usual, but in the “refers to” space, enter a formula instead of a cell reference.

The most common formula for a dynamic named range is the offset formula. This formula returns a range from a specific cell. This is a sample formula for a dynamic named range:

=OFFSET($A$1,1,0,MAX(1,COUNTA($A:$A)-1),MAX(1,COUNTA($1:$1)))

This formula basically states:

• Start in A1
• Go down 1 cell
• Go across 0 cells

Return a range that starts HERE, and has:

• Rows equal to 1 less than the number of values in column A (but at least 1) and
• Columns equal to the number of values in Row 1 (but at least 1)

In a standard table, with headings, starting in A1, this would return the entire table, not including the headings, and as rows or columns were added or removed from the table, the range would STILL return the entire table, and nothing but the table!

Why “at least 1”?

It is generally a bad idea to have named range (dynamic or otherwise) that does not actually return a range. Using this range would generate a #name error, instead of a more relevant error (such as #N/A for a vlookup). It can also cause VBA exceptions. Generally it is better to return a range.

Why start from A1, when we actually want from A2?

One of the “issues” with named ranges, is that if you delete the cell(s) the range refers to (the ACTUAL cells, not simply the contents), the named ranged will reference #REF and have to be manually adjusted. In this case, although the named range “starts” in A2, the reference uses A1, so as long as A1 remains present, the range will work correctly. If we had referenced A2, then deleting all rows in the table (a common enough occurrence) would “break” the named range.

Scope

In Excel, a named range can “belong” to either a workbook or a worksheet. The default is workbook and this is usually the better idea. Generally the only time I use a worksheet scoped named range is when I have very similar worksheets in a workbook and I want to use the same named range on multiple sheets.

Limitations

Unfortunately there ARE some limitations to named ranges:

Limited Naming Options

There are a variety of characters that cannot be used in the name of a named range, including spaces.

Array Formulae

These CANNOT be used to define a named range, which is unbelievably irritating.

Indirect

Indirect sadly does not play well with named ranges. You CANNOT use indirect to reference either a dynamic named range, or a non contiguous named range (one where the range does not form a perfect rectangle). Luckily, there is a VBA work around, which I will be going over in the next post.

Charts

Charts do not play well with named ranges. Unfortunately this is a lot harder to define than the issues with indirect as some things work and some things don’t. It also changes SIGNIFICANTLY between Office 2007 and Office 2010, so just be aware that they may not work the way you expect.