Please Note: This article is written for users of the following Microsoft Excel versions: 2007, 2010, 2013, 2016, 2019, and Excel in Office 365. If you are using an earlier version (Excel 2003 or earlier), this tip may not work for you. For a version of this tip written specifically for earlier versions of Excel, click here: Unbreakable Formula References to Worksheets.

Unbreakable Formula References to Worksheets

by Allen Wyatt
(last updated October 1, 2020)


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 (10812) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, and Excel in Office 365. You can find a version of this tip for the older menu interface of Excel 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. ...


Ignoring Other Applications

Do you want Excel to ignore other applications that may be running on your computer? You can configure the program to do ...

Discover More

Getting Rid of ScreenTips

All those little ScreenTips bug you when moving through the toolbars and ribbons of Word? You can turn them off by ...

Discover More

Adding an Inverted A

Want to insert an upside-down letter in your text? This tip provides a number of different approaches you can use to get ...

Discover More

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!

More ExcelTips (ribbon)

Finding a Worksheet to Unhide among Many Hidden Sheets

Trying to track down a single worksheet among many hidden worksheets can be a challenge. This tip examines a few ...

Discover More

Getting Rid of the Bothersome Lock Symbol

Microsoft added a new feature to Excel that causes a "lock icon" to appear at the left of a worksheet tab if the ...

Discover More

Returning a Worksheet Name

Need to know the name of the current worksheet? You can use the CELL function as the basis for finding this information ...

Discover More

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

View most recent newsletter.


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 6Mpixels. 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 less than 8?

2020-10-01 11:25:28

Tim Easter

I wonder whether Alan's problem is not that he renames the September worksheet to October, but that he adds a new worksheet named October and then wants to change the formula references on the Overview Report worksheet to find the data on the October worksheet. If so, your suggestions will still work for him. However, your suggestion to review how he is setting up his data is also probably relevant.

For a number of clients I have encouraged them to put all their data into one table that they keep adding to, then set up their reports to read that table (such as using a pivot table) and merely change the filter for the month they want to report on. This approach has resulted in a lot of other data analysis becoming possible because all their data is in one data table with no formatting applied to it, which is much more amenable to continued analysis than their previous method of storing data. Just a thought.

2020-07-29 03:05:44

Suroiu Stefan Ciprian

Hello there, I found a solution for "Unbreakable Formula References to Worksheets" but can be used if you use maxim two sheets: =INDIRECT("'*'!A1")

2020-05-20 10:43:43

Peter Atherton

Did J. Wooley's suggestion work? If not a UDF might do. You need a list of sheet names and a data Valididation cell listing them. See Macro below.
Use the SHEET() function to give tghe number of the active sheet.
Use the match function to get the position of the sheet you want (From the List) =MATCH($C$1,A1:A3)-SHEET()

Use this function (Code Below) sheetOffset(offset,ref) e.g. =IF(sheetoffset($C$2,A1)=0,"",sheetoffset($C$2,A1)) and copy down and across.

Sub ListSheets()
Dim sh As Worksheet, i As Integer
i = 1
For Each sh In ActiveWorkbook.Sheets
Cells(i, 1) = sh.Name & "!"
i = i + 1
End Sub

Function SHEETOFFSET(offset, Ref)
'returns cell content as ref, in sheet offset
'Source:= Excel 2002 Power Programming
'Author:= John Walkenbach
SHEETOFFSET = Sheets(Application.Caller.Parent.Index _
+ offset).Range(Ref.Address)
End Function

you can combine functions e.g. =SUM(sheetoffset(C2,A1:A3))

(see Figure 1 below)

Figure 1. 

2020-05-19 09:58:37

J. Woolley

Have you tried copying (Ctrl+C) the data from the new worksheet and pasting it (Ctrl+V) into the original worksheet referenced by the formula worksheet?

2020-05-18 09:51:34


Hi. I have an excel worksheet with formulas that references another worksheet with data.

When I replace (delete) the data worksheet with a new data worksheet, the cells within the formula worksheet become replaced by #ref!.

I then have to replace all the formulas and start again.

Is there a way to replace the data worksheet without corrupting the formula worksheet, and also have the formula worksheet refresh with the new data?

2019-10-16 13:24:34

J. Woolley

Read the Tip. Allen explains why Alan might be having the problem, then provides 4 ways to fix the problem.

2019-10-15 16:37:19


misleading title. in the first paragraph you present a problem then you present a solution that has nothing to do with the original problem. i thought this would be helpful as i have the exact problem being laid out in the first paragraph. you go on to say this isnt normal and go on to describe the INDIRECT() function. im not using that function. but neither is Alan from the info youve given to us. so how does alan fix his problem?

2015-07-17 03:03:46


Dear sir,
I have doubt in excel.kindly reply asap.

doubt:i have 3 sht in one workbook.i have taken range of cells from 2nd sht to sum(lets say A3:A5 which is empty)in 1st sht.And again in 2nd sheet in cell A2,i used if function to calculate the values from 1st sheet(I got answer till this).with the help of cell A2 in 2nd sht,i have to separate the value of A2 according to the percentage in the range(A3:A5)so that it can display value in 1st sht,which is already used for summing in 1st,eg:A2=140 if I give percentage (=A2*40%)in A3, the value is changing in A2=84, since because I have selected the range earlier for 1st sheet.but I don't want that change, I need it as(A2=140)with the value in A3=56.suggest me better a option

This Site

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

Newest Tips

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.