Please Note: This article is written for users of the following Microsoft Excel versions: 97, 2000, 2002, 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: Relative Worksheet References.

Relative Worksheet References

by Allen Wyatt
(last updated October 5, 2015)


Suppose you have a workbook with three worksheets, Sheet1, Sheet2 and Sheet3. In column A1 of worksheet Sheet2 you have the formula =Sheet1!A1. When you copy that formula from Sheet2 to cell A1 of Sheet3, the formula still references Sheet1. How can that be, though? Why doesn't Excel adjust the sheet reference, like it does the cell references?

Like named ranges, Excel treats worksheet names as absolute. Each worksheet object is independent of all other worksheets in the workbook. When you paste a formula that includes a sheet reference, that sheet reference is left unchanged in what is pasted.

There are a couple of things you can do. One is to simply modify the formula reference after it is pasted so that it references the correct sheet. If you have many of them to change, then you can select all the formulas in the target worksheet (F5 | Special | Formulas) and then use Find and Replace to replace the original worksheet name (Sheet1) with the correct worksheet name (Sheet2).

If your referencing needs are not complex, then you can use a macro approach. For instance, if you want a formula in a particular cell to refer to a cell on the sheet previous to the current sheet, then you can do that by macro rather easily. Consider the following macro:

Function PrevSheet(rCell As Range)
    Dim i As Integer
    i = rCell.Cells(1).Parent.Index
    PrevSheet = Sheets(i - 1).Range(rCell.Address)
End Function

The macro looks at the current worksheet and then figures out which worksheet is before it. The reference is then made for that worksheet. Once you've created the PrevSheet macro, here's one way the function can be used in a cell:


This returns the value of cell A1 from the previous worksheet. If you have Sheet1, Sheet2, and Sheet3, and you use this formula on Sheet3, then it returns the value of Sheet2!A1. If the previous sheet is the first sheet of the workbook or it is not a worksheet, then the function returns a #Value error.

If you later copy this formula to a different sheet (say to Sheet 5), then it pulls up the value relative to its new location, which means it pulls up the value from Sheet4!A1.

You can also include a sheet name and the function will work just fine:


This version will always return Sheet2!A5 since sheet2 is the previous sheet of Sheet3.


If you would like to know how to use the macros described on this page (or on any other page on the ExcelTips sites), I've prepared a special page that includes helpful information. Click here to open that special page in a new browser tab.

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

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. ...


Aligning Plus/Minus Symbols

Scientific writing often involves the use of special symbols, such as the plus/minus symbol. If you want to align these ...

Discover More

Setting Denominator Depth Spacing in the Equation Editor

How to change the distance between a fraction bar and the baseline of the number below it.

Discover More

Checking Your Data File

When you get ready to merge a document with a data source, you'll want to make sure that everything is "as expected" ...

Discover More

Professional Development Guidance! Four world-class developers offer start-to-finish guidance for building powerful, robust, and secure applications with Excel. The authors show how to consistently make the right design decisions and make the most of Excel's powerful features. Check out Professional Excel Development today!

More ExcelTips (menu)

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

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 ...

Discover More

Ordering Worksheets Based on a Cell Value

Need to sort your worksheets so that they appear in an order determined by the value of a cell on each worksheet? Using a ...

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 3 + 4?

2017-09-18 20:30:01


VOLATILITY! The bane of my life!

Can I replace the line:

Application.Volatile True


Application.Volatile False

I'm hoping to use this to replace the volatile version i have using INDIRECT() to essentially build a dynamic reference to previous and next sheet.
If PrevSheet() is volatile too, it's neater than my solution but doesn't help me escape the need for Manual calculations mode.

(Also if you have time, I need a NextSheet().

Can I basically change both occurances of the name itself and change one '-' to a '+':
PrevSheet = Sheets(i - 1).Range(rCell.Address)


NextSheet = Sheets(i + 1).Range(rCell.Address)

Sorry if it's more complicated than that. I'm just guessing and have zero experience with VBA.

Or I could use Peter Moran's RelSheet() instead. Could anyone confirm that it works. No feedback or comments on his one yet...

Thanks in advance! VERY useful!

2017-08-10 09:29:00


Thank you for this. I had a series of sheets that I used to do debt analysis (interest, payoffs, etc) and if I wanted to move the sequence I paid them off in, I had to modify 3 of the sheets to point to new sheets. It was only a couple of formulas, but now I just put them in the correct order and everything gets calculated the way I want.

The way I use this is when the first debt gets paid off, the second debt picks up its payment and starts paying extra. All the way down the line. I have it ordered with the highest interest rate first and the lowest interest rate last. Now it's easy to reorder and see the new pay off dates.

2017-04-01 04:18:32

Alan Elston

@Rick. Hi Rick. Sorry but I think I am not following you. I think =!A1 or =!$A$1 will error when written in any cell. I think also !A1 or !$A$1 in any formula will also error.
Simply writing A1 or $A$1 in a cell is referencing to the Worksheet in which it is in, so I suppose you could think of it as pseudo referencing to the active Worksheet.
But I think the point of this blog is to allow the Worksheet reference to change in a similar way to a relative cell reference. Allen used the example of the Worksheet reference always going to the previous worksheet.
That stackoverflow post showed an interesting way to always refer to the current cell in a formula, without using something involving INDIRECT(). That seemed to somehow create a relative RefrersTo:= in a named range object, making the named range dynamic in terms of where it refers to. The point then is that !A1 is what you use for the RefrersTo:= so as to get the named range to always refer to itself. For that to work, you must use the relative reference of A1 and be selecting cellA1 as you do it. ( or use the relative reference for any cell, as long as you are selecting that cell as you create the named range object ) . Using the absolute reference of !$A$1 would not work.

2017-03-31 16:12:35


Used this. Thanks!

2017-03-10 09:53:53


There is no need to use VBA to do this. Simply use relative sheet references in the formula instead of sheet names, like so:




Now when copied and pasted, the formula will refer to cell A1 in the active sheet.

See this stackoverflow question for more information:

2017-02-11 07:08:39

Alan Elston

that shouod have read...
.....only interested in copying values ( and not....

2017-02-11 07:06:28

Alan Elston

Hi Denis
If you are only interested in copying , ( and not many Properties including things such as Format ) , then using the .Value property is usually the best suited.

The .Value Property will present the .Value value or values which may be assigned directly to the corresponding value or values of any other Range object

Sub ValueToValue()
Let ThisWorkbook.Worksheets.Item("Sheet2").Range("B5").Value = ThisWorkbook.Worksheets.Item("Sheet1").Range("AN5").Value
End Sub

The Worksheets collection Object of the Workbook in which the code is in ( ThisWorkbook ),
Is used to refer to a worksheet item via its ( Tab ) Name.
The Range(“”) Property is used to refer to the Range object associated with a particular cell.
The .Value Property, as explained above

Although the code strictly speaking assigns ( fills ) the .Value Property of a Range object, ( which is held in memory god knows where and how ), Excel displays the .Value Property typically in the cell for us to “see”


2017-02-10 12:23:18


How do I move a value from one worksheet to another in the same excel book. Example I want to move the value of AN5 from sheet 1 to B5 on sheet 2

2016-12-20 07:07:11


who know ?
how to use returns of spesial function
inside formula of cell?

I have formula
=TRANSPOSE('[B813-32 after Vibro.xlsx]Blade-6'!$B$13)

I need to change name of sheet in formula to name of "next sheet"

F EXM: Blade-6 TO Blade-7

2016-10-04 18:17:02


It would be nice if you said where and how you put this code into the spreadsheet. I'm trying to find online where I stick this code and all I find is how to add buttons and other controls to the spreadsheet.

2016-04-20 09:31:18


can I get a help to find a range of Prevsheet

for a cell it worked very well

but it didn't work to do Vlookup

2015-09-30 10:12:32



Can this macro be combined with vlookup in order to provide a value from a previous sheet?


2015-08-26 02:48:12

Rajeswar Rao NLV

I have tried this exact macro and it worked for me. It has saved me from enormous work. PrevSheet function is working fine in Macro enable workbook of office 2007 and 2010.
Thank You ExcelTips.

2015-08-13 09:31:31

Moosey Jo

I have a workbook that has many many references to other sheets within the same workbook. At the end of every month, I change the file name of the workbook to the new month, then all of my worksheet links refer back to the previous month workbook.

How do I make it so they always just refer to the current workbook?


2015-04-16 11:22:35

Anshul Joshi

i have to compile multiple files which are extracted from other software file formate is ".asc". files generally saved with date.

i have been import all files in one workbook with their respective name.

Now, i want to do averaging all sheets and compile in one master sheet.

kindly advice

2015-01-08 18:29:12


I have worksheet with three sheets , one sheet is called casepick, replen and templates. from both casepick and replen i want to refer to template to copy range of format. this works fine for the casepick but does not work from replen. here is ample macro:

I want to change casepick to sheets that you have come from, let me know how to do this.

2014-11-03 09:04:00


Here is the solution that doesn't care about cell location or formula in the cell.

hope this helps some who wants to advance the tab & cell values by referencing previous tabs information.

examples of various cell formulas & values

=JUL31_14!H50+E32 <--------- NOTE the tab name and cell are the previous sheet values this is what needs to be altered

Macro works with other workbooks open.

Sub CopyRename()
Dim sName As String
Dim wks As Worksheet
ActiveSheet.Copy After:=Sheets(Worksheets.Count)
Set wks = ActiveSheet
Do While sName <> wks.Name
sName = Application.InputBox _
(Prompt:="Enter new worksheet name")
On Error Resume Next
wks.Name = sName
On Error GoTo 0
Cells.Replace What:=Application.InputBox _
(Prompt:="Enter Tab Name of previous worksheet NOT COPIED"), Replacement:=Application.InputBox _
(Prompt:="Enter Tab Name of WORKSHEET YOUR COPYING"), LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
Set wks = Nothing
End Sub



2014-11-02 02:49:03



Can the cell populate/revise when in the process of creating a new tab?

It would be great if I could incorporate the ctrl+h somehow, so the input box can ask & cell location is not a concern. or just rev-up / copy-replace old with correct name.

have been trying to get this macro to work and it seems to have a cpl glitches.

Sub CopyRename()
Dim sName As String
Dim wks As Worksheet
ActiveSheet.Copy After:=Sheets(Worksheets.Count)
Set wks = ActiveSheet
Do While sName <> wks.Name
sName = Application.InputBox _
(Prompt:="Enter new worksheet name")
On Error Resume Next
wks.Name = sName
On Error GoTo 0
Cells.Replace What:="(*!", Replacement:=sName = Application.InputBox(Prompt:="Enter COPIED worksheet name w/!"), LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
Set wks = Nothing
End Sub

Any help would be awesome,

Thank you.

2014-10-28 16:16:30


I use ='main sheet name'!$A$1:$A$90 and it works fine when on the same cell range; ie, A1 to A1, etc. However, when I attempt to go to a different cell and use the following on N1
='main sheet name'!$AF$1:$AG$90

- it generates a Value error.

Any help?

2014-08-02 06:06:48


This solution is good.

Function PrevSheet(rCell As Range)
Dim i As Integer
i = rCell.Cells(1).Parent.Index
PrevSheet = Sheets(i - 1).Range(rCell.Address)
End Function
But it only works in one open workbook at a same time.
I mean when I use it in more than one file eg: workbook1 and workbook2 it will calculate wrong cell from another workbook and vice versa.

Please help me to edit this function.
thank you

2014-07-05 21:29:56


Thank you, very simple solution to a time consuming task. This also takes a lot of the errors from omission out of the task.

It is a macro and needs to be created in a Code Module. That is the only trouble I had, lack of experience!

2013-12-07 18:48:34


Thank you, thank you, thank you! You just saved me a ton of time. This is the easiest and most straightforward solution I've found on this subject.

2013-06-28 12:58:31


This tip works, tyvm. However, Quattro Pro allows the sheet reference to change, making life easier in long run.

2013-04-24 16:20:19


I am in great need of this macro for a spreadsheet I'm creating. I want cell E5 to equal cell E5 of the worksheet directly to the left of any given worksheet.
I could not get the first macro to work...and I'm not sure how to enter the second macro.
Any help would be greatly appreciated!

2012-09-22 11:31:24

Peter J Moran

Hi Allen,

Your tip only allows reference to one of possibly many sheets in a workbook.

This is a more flexible solution allowing reference to any other sheet in a workbook, but requires care in understanding the effects of inserting sheets!

Public Function RelSheet(iPos As Integer, zRange As String) As Variant

'Relative Worksheet Reference Facility
'eg: =RelSheet(-1,"A3") = Cell A3 in Previous (Left) WSheet
'eg: =RelSheet(1,"A3") = Cell A3 in Next (Right) WSheet
'eg: "#Error" when reference does not exist
'eg: Can do maths =RelSheet(1,"A3")*2

Dim shtActive As Worksheet
Application.Volatile True
Set shtActive = Application.Caller.Worksheet
On Error GoTo BadSheetReference
RelSheet = Sheets(shtActive.Index + iPos).Range(zRange).Value

GoTo ExitFunction


RelSheet = "#Error"


End Function

Peter Moran

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

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.