Worksheet Level

Worksheet level named ranges are worksheet specific and are normally only used on the worksheet where they have been defined.
They do allow you to use the same named range on different worksheets but they are only displayed when that particular worksheet is active.
The (Insert > Name > Define) dialog box only displays "worksheet level" named ranges for the active worksheet.
These can only be created by preceding the named range with an exclamation mark followed by the name of the worksheet.
It is possible to refer to these from other worksheets, but they must be preceded with the worksheet name (e.g. =Sheet1!Named_Range).
Using worksheet and workbook level names in the same workbook can get a bit confusing.

Displaying the Named Ranges

These only appear in the Name box when that particular worksheet is active.
These are prefixed with the name of the worksheet when viewed in the (Insert > Name > Define) dialog box.
When you display the (Insert > Name > Define) dialog box any worksheet level named ranges have their respective worksheet name on the right hand side.
Worksheet level named ranges will only appear in this dialog box when the current worksheet is active.

Editing Named Ranges

You cannot use the Name Box to redefine any existing named ranges. This has to be done from the (Insert > Name > Define) dialog box.
You can redefine your named ranges using the (Insert > Name > Define) dialog box.
Select the named range from the list and edit the cell reference in the Refers to box.
You can either type in the new reference or you can select a range of cells directly.

Removing a Worksheet Level Named Range

Display the (Insert > Name > Define) dialog box.
Select the named range from the list and press Delete.
If you want to rename a named range then you can select (Insert > Name > Define) and change the name in the text box.

Important

Avoid copying a formula that includes a named range from one workbook to another as this creates a hidden link between the two workbooks.
If you have formula using a named range and then delete the named range, the formula will return the #NAME? Error.
Remember to use the arrow keys to manoeuvre within the named range formula and not the mouse.
It is possible to use worksheet specific named ranges on other worksheets by prefixing the named range with its worksheet name (e.g. Sheet2!Sheet2_B3).
Only the worksheet level named ranges on the active worksheet are displayed in the Name Box and in the (Insert > Name > Define) dialog box.
If you create a named range and then realise that you want to change the name you must delete the old name and create the named range again.
If the named range already exists you cannot use the Name Box to change the reference.