Potential Problems with Names


Names are great, but they can also cause some problems. This section contains information that you should remember when you use names in a workbook.

Name Problems When Copying Sheets

Excel lets you copy a worksheet within the same workbook or to a different workbook. Focus first on copying a sheet within the same workbook. If the copied sheet contains worksheet-level names, those names will also be present on the copy of the sheet, adjusted to use the new sheet name. Usually, this is exactly what you want to happen. However, if the workbook contains a workbook-level name that refers to a cell or range on the sheet that's copied, that name will also be present on the copied sheet. However, it will be converted to a worksheet-level name! That is usually not what you want to happen.

Consider a workbook that contains one sheet (Sheet1). This workbook has a workbook- level name (BookName) for cell A1, and a worksheet-level name (Sheet1!LocalName) for cell A2. If you make a copy of Sheet1 within the workbook, the new sheet is named Sheet1 (2). You'll find that, after copying the sheet, the workbook contains four names, as shown in Figure 3-14.

image from book
Figure 3-14: Copying a worksheet creates duplicated names.

This proliferation of names when copying a sheet is not only confusing, but it can result in errors that can be very difficult to identify. In this case, typing the following formula on the copied sheet displays the contents of cell A1 in the copied sheet:

 =BookName 

In other words, the newly created worksheet-level name (not the original workbook-level name) is being used.

If you copy the worksheet from a workbook containing a name that refers to a multisheet range, you also copy this name. A #REF! error appears in its Refers To definition.

When you copy a sheet to a new workbook, all the names in the original workbook that refer to cells on the copied sheet are also copied to the new workbook. This includes both workbook-level and worksheet-level names.

Note 

Copying and pasting cells from one sheet to another does not copy names, even if the copied range contains named cells.

Bottom line? You must use caution when copying sheets from a workbook that uses names. After copying the sheet, check the names and delete those that you didn't intend to be copied.

Name Problems When Deleting Sheets

When you delete a worksheet that contains cells used in a workbook-level name, you'll find that the name is not deleted. The name remains with the workbook, but it contains an erroneous reference in its Refers To definition.

Figure 3-15 shows the Name Manager dialog box that displays an erroneous name. The workbook originally contained a sheet named Sheet1, which had a named range (a workbook-level name, MyRange) for A1:F12. After deleting Sheet1, the name MyRange still exists in the workbook, but the Refers To field displays the following:

 =#REF!$A$1:$F$12 

image from book
Figure 3-15: Deleting the sheet that contains the cell for MyRange causes an erroneous reference.

image from book
Naming Objects

When you add an object to a worksheet (such as a shape, or clip art), the object has a default name that reflects the type of object (for example, Rectangle 3 or Text Box 1).

To change the name of an object, select it, type the new name in the Name box, and press Enter. Naming charts is an exception. To rename a chart, use the Chart Tools image from book Layout image from book Properties image from book Chart Name control.

Excel is a bit inconsistent with regard to the Name box. Although you can use the Name box to rename an object, the Name box does not display a list of objects. Excel also allows you to define a name with the same name as an object, and two or more objects can even have the same name. The Name Manager dialog box does not list the names of objects.

image from book

As far as I can tell, keeping erroneous names in a workbook doesn't cause any harm, but it's still a good practice to delete or correct all names that contain an erroneous reference.




Excel 2007 Formulas
Excel 2007 Formulas (Mr. Spreadsheets Bookshelf)
ISBN: 0470044020
EAN: 2147483647
Year: 2007
Pages: 212

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net