Please Note: This article is written for users of the following Microsoft Excel versions: 97, 2000, and 2003. If you are using a later version (Excel 2007 or later), this tip may not work for you. For a version of this tip written specifically for later versions of Excel, click here: Unbreakable Formula References to Worksheets.

Unbreakable Formula References to Worksheets

by Allen Wyatt
(last updated December 27, 2016)

Alan has a workbook that has two worksheets: "September Data" and "Overview Report." On the second worksheet he has references, within formulas, to cells on the first worksheet. When a new month comes around, Alan needs to change the name of the "September Data" worksheet to "October Data," which breaks all the formulas on the other worksheet. He wonders how he can change the formulas that reference the first worksheet so that they don't break when he changes the first worksheet's name.

First of all, it should be said that this behavior (as described) is not normal for Excel. If you are in the Overview Report worksheet and you create a formula that references a cell on the September Data worksheet, then any changes to the name of the September Data worksheet should be automatically reflected in the formulas in any other worksheets in the workbook. The only time this isn't the case is if you have a formula that uses the INDIRECT function to reference something on the worksheet, as exampled here:

=INDIRECT("'September Data'!A3")

The reason it doesn't change is because the worksheet name is contained within single quote marks (apostrophes), meaning it is viewed as a string constant that should not be changed. If you need to use INDIRECT, then place the worksheet name off in some other cell and reference that cell in the INDIRECT formula, as described in other issues of ExcelTips. You can then change the contents of the referenced cell to reflect the name of the worksheet you want to use.

If you don't want to change your formulas in this way, then you can use Find and Replace to change the references after you rename the September Data worksheet. Follow these steps:

  1. Display the second worksheet (Overview Report).
  2. Press Ctrl+H to display the Replace tab of the Find and Replace dialog box.
  3. Click the Options button, if it is visible. (See Figure 1.)
  4. Figure 1. The Replace tab of the Find and Replace dialog box.

  5. In the Find What box enter "September Data" (without the quote marks).
  6. In the Replace With box enter "October Data" (without the quote marks).
  7. Make sure the Look In drop-down list is set to Formulas.
  8. Click Replace All.

Another option that works well is to simply define named ranges in the September Data worksheet. Each cell (or range of cells) should be assigned a name that you can then use in formulas on other worksheets. Any changes to the worksheet name will not affect the use of the named ranges at all.

Finally, you could consider looking at your data layout. For instance, you might be better off to not have a worksheet named "September Data." Instead, create a worksheet named "Current Month" and reference it in your formulas. When the start of a new month rolls around, copy the data from the Current Month worksheet and archive it in a new worksheet that bears the name of the month it represents (such as "September Data"). This way you're always working with the current data and have the older months' data available for review at any time you need.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (10811) applies to Microsoft Excel 97, 2000, and 2003. You can find a version of this tip for the ribbon interface of Excel (Excel 2007 and later) here: Unbreakable Formula References to Worksheets.

Author Bio

Allen Wyatt

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He  is president of Sharon Parq Associates, a computer and publishing services company. ...

MORE FROM ALLEN

Changing the Return Address Location

When Word creates envelopes for you, there may be times that you don't like where it places the return address. Repositioning ...

Discover More

Specifying the Default Printer

Windows allows you to define many printers that can be used with your system. Chances are good that one of those printers is ...

Discover More

Replacing Highlighted Words

The Find and Replace capabilities of Word are quite powerful. Knowing how to find and replace highlighted text can be a bit ...

Discover More

Create Custom Apps with VBA! Discover how to extend the capabilities of Office 2013 (Word, Excel, PowerPoint, Outlook, and Access) with VBA programming, using it for writing macros, automating Office applications, and creating custom applications. Check out Mastering VBA for Office 2013 today!

More ExcelTips (menu)

Identifying the Last Cell Changed in a Worksheet

When someone changes a cell in a worksheet, Excel normally goes along its merry way of keeping everything up to date. It does ...

Discover More

Determining a Worksheet's Number

When you add a new worksheet to a workbook, it receives a meaningful name such as "Sheet4" or "Sheet17." If you want to ...

Discover More

Viewing Same Cells on Different Worksheets

When switching from one worksheet to another, you might want to view the same portion of the new worksheet that you were ...

Discover More
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

View most recent newsletter.

Comments

If you would like to add an image to your comment (not an avatar, but an image to help in making the point of your comment), include the characters [{fig}] in your comment text. You’ll be prompted to upload your image when you submit the comment. Maximum image size is 8Mpixels. Images larger than 600px wide or 1000px tall will be reduced. Up to three images may be included in a comment. All images are subject to review. Commenting privileges may be curtailed if inappropriate images are posted.

What is eight minus 3?

There are currently no comments for this tip. (Be the first to leave your comment—just use the simple form above!)


This Site

Got a version of Excel that uses the menu interface (Excel 97, Excel 2000, Excel 2002, or Excel 2003)? This site is for you! If you use a later version of Excel, visit our ExcelTips site focusing on the ribbon interface.

Newest Tips
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

(Your e-mail address is not shared with anyone, ever.)

View the most recent newsletter.