Loading
Excel.Tips.Net ExcelTips (Menu Interface)

Relative Worksheet References

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.

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.

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:

Saif    20 Apr 2016, 09:31
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)
Garion    30 Sep 2015, 10:12
Hello,

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

Thanks,
Rajeswar Rao NLV    26 Aug 2015, 02:48
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.
Moosey Jo    13 Aug 2015, 09:31
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
Anshul Joshi    16 Apr 2015, 11:22
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
kal    08 Jan 2015, 18:29
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
Jim    03 Nov 2014, 09:04


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
Jim    02 Nov 2014, 02:49
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
Pris    28 Oct 2014, 16:16
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?
Ali    02 Aug 2014, 06:06
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
Paul    05 Jul 2014, 21:29
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!
Angel    07 Dec 2013, 18:48
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.
Greg    28 Jun 2013, 12:58
This tip works, tyvm. However, Quattro Pro allows the sheet reference to change, making life easier in long run.
Jacki    24 Apr 2013, 16:20
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
Peter J Moran    22 Sep 2012, 11:31
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
 
 

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.