Loading
Excel.Tips.Net ExcelTips (Menu Interface)

Unbreakable Formula References to Worksheets

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.

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.

Related Tips:

Solve Real Business Problems Master business modeling and analysis techniques with Excel and transform data into bottom-line results. This hands-on, scenario-focused guide shows you how to use the latest Excel tools to integrate data from multiple tables. Check out Microsoft Excel 2013 Data Analysis and Business Modeling today!

 

Leave your own comment:

*Name:
Email:
  Notify me about new comments ONLY FOR THIS TIP
Notify me about new comments ANYWHERE ON THIS SITE
Hide my email address
*Text:
*What is 5+3 (To prevent automated submissions and spam.)
 
 
           Commenting Terms

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!)
 
 

Our Company

Sharon Parq Associates, Inc.

About Tips.Net

Contact Us

 

Advertise with Us

Our Privacy Policy

Our Sites

Tips.Net

Beauty and Style

Cars

Cleaning

Cooking

DriveTips (Google Drive)

ExcelTips (Excel 97–2003)

ExcelTips (Excel 2007–2016)

Gardening

Health

Home Improvement

Money and Finances

Organizing

Pests and Bugs

Pets and Animals

WindowsTips (Microsoft Windows)

WordTips (Word 97–2003)

WordTips (Word 2007–2016)

Our Products

Helpful E-books

Newsletter Archives

 

Excel Products

Word Products

Our Authors

Author Index

Write for Tips.Net

Copyright © 2016 Sharon Parq Associates, Inc.