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)

24

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)
    Application.Volatile
    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:

=PrevSheet(A1)

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:

=PrevSheet(Sheet3!A5)

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

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

MORE FROM ALLEN

Searching for Styles

If you use styles to format your text, you can later search for words and phrases that are formatted using various styles. ...

Discover More

Creating See-through Text Boxes

When laying out your document, you may want to use a text box that appears to be positioned over your text, but to be ...

Discover More

Fixed-Width Settings when Converting Text to Columns

The Convert Text to Columns capabilities of Excel are very helpful when pulling apart information. When working with ...

Discover More

Excel Smarts for Beginners! Featuring the friendly and trusted For Dummies style, this popular guide shows beginners how to get up and running with Excel while also helping more experienced users get comfortable with the newest features. Check out Excel 2013 For Dummies today!

More ExcelTips (menu)

Protecting a Worksheet's Format

You can protect various parts of your worksheets by using the tools built into Excel. One thing you can protect is the format ...

Discover More

Jumping to Alphabetic Worksheets

Got a workbook with a lot of worksheets in it? Here's some handy ways to jump to the worksheet you want, alphabetically.

Discover More

Detecting Types of Sheets in VBA

When processing workbook information in a macro, you may need to step through each worksheet to make some sort of changes. It ...

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 6 - 3?

2017-08-10 09:29:00

JohnF

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


2017-03-31 16:12:35

David

Used this. Thanks!


2017-03-10 09:53:53

Rick

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

=!A1

OR

=!$A$1

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

See this stackoverflow question for more information:

http://stackoverflow.com/questions/757413/excel-reference-to-current-cell


2017-02-11 07:08:39

Alan Elston

EDIT:
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”

Alan


2017-02-10 12:23:18

Denis

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

MIHA

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

Hayden

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

Saif

Gents
can I get a help to find a range of Prevsheet

for a cell it worked very well
=PrevSheet(A1)

but it didn't work to do Vlookup
=VLOOKUP($A2,PrevSheet!$A$2:$J$215,1,0)


2015-09-30 10:12:32

Garion

Hello,

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

Thanks,


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?

Moosey


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

kal

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:
Sheets("Templates").Select
Range("A13:AD15").Select
Selection.Copy
Sheets("casepick").Select
ActiveSheet.Paste

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


2014-11-03 09:04:00

Jim



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
=SUM((OCT15_14!E45)+E46-E10-E42)
=SUM(OCT15_14!E49+E39+E40+E41+E42)-E11+E50

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
Loop
Range("A1").Select
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, _
ReplaceFormat:=False
Set wks = Nothing
End Sub


Regards,

Jim


2014-11-02 02:49:03

Jim

Hi,

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
Loop
Cells.Replace What:="(*!", Replacement:=sName = Application.InputBox(Prompt:="Enter COPIED worksheet name w/!"), LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Set wks = Nothing
End Sub

Any help would be awesome,

Thank you.
Jim


2014-10-28 16:16:30

Pris

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

Ali

Hi
This solution is good.

---------------------
Function PrevSheet(rCell As Range)
Application.Volatile
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

Paul

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

Angel

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

Greg

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

Jacki

Hello!!
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!
Thanks!
Jacki


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

BadSheetReference:

RelSheet = "#Error"

ExitFunction:

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