Troubleshooting

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.

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)".

???

Link to cells with Named Ranges, the named ranges are automatically replaced ??
A big help with absolute/relative references when using AutoFill ??

Non Contiguous Named Ranges

It is possible to name non continuous cells as well.
Hold down the Ctrl key to select several individual cells (or ranges) before displaying the (Insert > Name > Create) dialog box.
This is often used to find the sum of various cells that contain totals.

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.

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.

Important

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.