Leading the way in Microsoft Office Development

Fixed Address Named Ranges

It is possible to create a named range that always refers to a specific cell location (as opposed to a specific cell).

If you define a named range in the usual way to a cell "E3" and you then insert a row at row 2, this named range will then refer to cell E4.

Likewise if you delete a row above this cell, it will then refer to cell "E2".

Using the INDIRECT() function it is possible to create a named range that will always refer to cell "E3" regardless of whether any rows or columns are inserted or deleted.

Use the following formula in a worksheet level named range "=INDIRECT("$E$3")".

Same Cell on All Worksheets

It is possible to define a workbook level named range that will refer to the same cell on any worksheet.

Using the OFFSET() function it is possible to create a named range that always refers to cell "A5".

Use the following formula in a workbook level named range “=OFFSET(!$A$5,,,,)”.

Regardless of which worksheet is currently displayed this named range will always refer to cell "A5" on the active sheet.

Displaying Named Ranges

When you set the Zoom percentage of a worksheet to 39% or less all the named ranges that consist of two or more adjacent cells will appear in rectangles on the screen.

Range names are sometimes displayed on your worksheets if they cover a large area ??

Workbook and Worksheet Named Ranges

If you define workbook and worksheet level named ranges with the same name, then only the workbook level named range will appear in the (Insert > Name > Define) dialog box.

To refer to a worksheet level named range you must precede the name with the workbook name ???

Using Named Ranges in other Workbooks

It is possible to reference named ranges in other workbook in an identical way to referencing cell ranges.

=Workbook.xls!Wbk_NamedRange*20

Naming whole Rows and Columns

It is possible to name an entire row or column. This can be useful when the data increases over time and you want to use a formula to refer to all the data.

To define a named range that refers to the whole of column B enter the following into the Refers to box "=Sheet1!$B:$B".

To define a named range that refers to the whole of row 5 enter the following into the Refers to box "=Sheet1!$5:$5".

You can create a circular named range reference to itself.

You can include a VLOOKUP function in a dynamic formula named range, “=VLOOKUP(C1,datatype,K2)".

Deleting Named Ranges

If you delete a named range which is being used in a formula the formula will return #NAME?.

You cannot undo the deletion od a named range using (Edit > Undo).

If you delete rows and columns that contain named cells or ranges then the named ranges will become invalid.

The cell references used by the named ranges will then contain #REF!.

In this case the named ranges are not automatically deleted.

Named Ranges firing events

It is possible to declare named ranges and have them executed as events.

You should try and avoid using any of these names unless you specifically want to use them for their intended purposes.

Excel's Automatic Named Ranges

You should try and avoid using any of these names unless you specifically want to use them for their intended purposes.

Very Long Named Ranges

A named range can contain up to 255 characters although any names longer than 253 cannot be selected using the Name Box.

Jumping to Visual Basic Editor

If you type in the name of a public subroutine (that is contained in the active workbook) into the Name Box then you are taken to the VBA subroutine in the Visual Basic Editor.

Things to Remember

Named ranges can be just a single letter (except for R and C) although this is not recommended.

Named ranges can be a maximum of 255 characters, although any names longer than 253 characters cannot be selected from the Name box.