XL: Errors When Worksheet Name Contains Apostrophe

ID: Q107468

The information in this article applies to:

Microsoft Excel for Windows, versions 5.0, 5.0c
Microsoft Excel for Windows 95, versions 7.0, 7.0a
Microsoft Excel for the Macintosh, versions 5.0, 5.0a
Microsoft Query for Windows, version 1.0
Microsoft Query for Windows 95, version 2.0

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 '1995''s 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

That name is not valid.

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

Keywords : kbtool xlquery
Version : WINDOWS:5.0,5.0c,7.0; MACINTOSH:5.0,5.0a
Platform : MACINTOSH WINDOWS
Issue type :