XL: Errors When Worksheet Name Contains Apostrophe

• 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

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

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.

Workaround:

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.

Workaround:

To create a local name on a worksheet when the worksheet name contains an apostrophe, do the following:

1. Rename the worksheet with a name that does not contain an apostrophe.

2. Create the local 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.

Workaround:

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