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 Office Assistant

The Office Assistant is part of the Help system available in Excel. If you want, you can change which Office Assistant ...

Discover More

Converting Coded Dates into Real Dates

Sometimes the format in which you receive data is not the same format that would be optimal for Excel. For instance, you ...

Discover More

Word Won't Capitalize Some Sentences

By default, Word capitalizes the first letter of sentences as you type. If you notice that Word doesn't capitalize some ...

Discover More

Comprehensive VBA Guide Visual Basic for Applications (VBA) is the language used for writing macros in all Office programs. This complete guide shows both professionals and novices how to master VBA in order to customize the entire Office suite for their needs. Check out Mastering VBA for Office 2010 today!

MORE EXCELTIPS (MENU)

Setting the Number of Default Worksheets

Excel allows the user to determine how many default worksheets are in a new workbook.

Discover More

Freezing Top Rows and Bottom Rows

Freezing the top rows in a worksheet so that they are always visible is easy to do. Freezing the bottom rows is not so easy. ...

Discover More

Quickly Inserting a New Worksheet

Want a quick way to insert a worksheet? There's nothing faster than using the handy shortcut.

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 for this tip:

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.

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.

Links and Sharing
Share