Article ID: 107468
Article Last Modified on 8/15/2003
APPLIES TO
- Microsoft Excel 95a
- Microsoft Excel 5.0c
- Microsoft Excel 5.0a for Macintosh
- Microsoft Query 2000
This article was previously published under Q107468
SUMMARY
In Microsoft Excel, you may be unable to complete certain actions or use certain add-in functions on worksheets when the name of the worksheet contains an apostrophe (').
MORE INFORMATION
In Microsoft Excel, the apostrophe is a special character you can use in references. If you include a workbook path in the reference, or if the workbook or sheet name contains space characters, or if the sheet name begins with a number, you must enclose the path, filename, and sheet name in apostrophes. For example, a formula that refers to cell A1, on a worksheet called 1994 Figures, in BOOK1.XLS, would appear as follows:
='[BOOK1.XLS]1994 Figures'!$A$1
Note that the apostrophes enclose the workbook and worksheet names. The apostrophes are necessary because the space between 1994 and Figures could be interpreted as a divider between items.
If the name of your worksheet contains an apostrophe, and you want to reference a range on this worksheet, you must add another apostrophe to the reference in order to distinguish the apostrophe in the name from apostrophes that enclose a name. For example, to reference cell A1 on the worksheet "1995's Forecasts" in BOOK1.XLS, use the following formula:
='[BOOK1.XLS]1995''s Forecasts'!$A$1
If your worksheet name contains an apostrophe, you may be unable to complete certain operations that use the worksheet name. Examples of these operations are shown below.
In general, avoid using apostrophes in your worksheet names.
EXAMPLES
Print Area not Retained in a View
If the name of your worksheet contains an apostrophe, and you create a view, using View Manager, the print area that is set for the view may not be retained.
WORKAROUND
To retain the print area set for a view created with View Manager, you must rename the worksheet with a name that does not contain any apostrophes, and then you need to recreate the view.
Cannot Delete Locally Defined Names
If the name of your worksheet contains an apostrophe, you cannot delete locally defined names on the worksheet. when you select the name in the Define Name dialog box and choose Delete, you receive the error message "That name is not valid" appears and the name is not deleted.
To delete a locally defined name, such as '1995s Forecasts'!Print_Area, you must rename the worksheet with a name that does not contain any apostrophes.
Cannot Create Locally Defined Names
If you try to create a local name on a worksheet when the worksheet name contains an apostrophe, you receive the following error message
and the name will not be added.
To create a local name on a worksheet when the worksheet name contains an apostrophe, do the following:
- Rename the worksheet with a name that does not contain an apostrophe.
- Create the local names.
NOTE: Once you have defined your local names, if you rename the sheet to the name with the apostrophe, you may receive the error message "Cannot Delete Locally Defined Names."
Cannot Return Data from Microsoft Query
If you use the Get External Data command, and you return query data from Microsoft Query to a worksheet name contains an apostrophe (or a space), the data is instead returned to the worksheet that was active when you chose the Get External Data command.
To avoid this behavior, do either of the following:
- Activate the worksheet with the name that contains the apostrophe (or space) before you click the Get External Data command.
-or-
- Rename the worksheet to a name that does not contain an apostrophe or a space.
REFERENCES
"User's Guide," version 5.0, pages 144-149
Additional query words: 5.00a 5.00c 7.00a XL7 XL5 XL
Keywords: KB107468
