Referring to Previous Sheet

Posted on by jkpieterse

For starters, let me thank Dick for opening his Blog to us. It prevents me having to go through all the trouble of starting one myself :-).

When referring to worksheets in a formula, it may sometimes be useful to have a method to refer to the sheet to the immediate left or right of the worksheet that contains the formula, regardless whether the worksheets are moved. Unfortunately Excel provides no direct method to do this.

There are a couple of ways one can do this. The obvious one is to use a VBA function that gets the previous or next worksheet’s name. But I thought it might be instructive to show a non-VBA solution that involves the use of (ancient) XL4 Macro functions in defined names.

Now let us get this up and running.

Start Excel and open the workbook in which you want to start using this.

Now define these names (Insert, Name, Define):

AllSheets
=GET.WORKBOOK(1+0*now())

Gets an array of all sheets in the workbook

ThisSheet
=GET.CELL(32+0*now(),indirect("rc",False))

Gets the name of the sheet the name is used in.

PrevSheet
=INDEX(AllSheets,MATCH(ThisSheet,AllSheets,0)-1+0*now())

Gives the name of the worksheet to the immediate left of the sheet where this name is used.

NextSheet
=INDEX(AllSheets,MATCH(ThisSheet,AllSheets,0)+1+0*now())

Gives the name of the worksheet to the immediate right of the sheet where this name is used.

The 0*Now() is added to ensure the names are “volatile” and get recalculated on every calculation of Excel.

To refer to cell B1 on the previous worsheet, use this formula:
=INDIRECT("'"&PrevSheet&"'!"&CELL("address",B1))

Likewise for the next worksheet:
=INDIRECT("'"&NextSheet&"'!"&CELL("address",B1))

To get the name of the previous sheet in a cell, use:
=PrevSheet

One serious warning is needed though.

Do not copy any cell that uses any of these defined names to another worksheet. Excel will crash!

Jan Karel Pieterse
www.jkp-ads.com

18 thoughts on “Referring to Previous Sheet”

  1. Hi Jan Karel
    nice one!. Just a comment regarding your warning: Excel 2003 seems to have solved this bug. So in Excel 2003 copying of these cells does not lead to a crash.

  4. wow, this goes way back to befor i even knew about excel !

    played around with them a bit, work well, but i don’t think i’m gonna risk it! ;-) one sheet out of place and there hell to pay, he,he.

    Cool stuff though.

  7. I’ve been using these macros and they’re working reall well. Thanks for this useful tip. But something goes wrong when the file is saved with (e.g.) a space in the filename.

    Here is my use of “PrevSheet”
    =INDIRECT(“”&PrevSheet&”!”&CELL(“address”,C35))
    and it’s evaluated as
    =INDIRECT(“”&[TEST WITH SPACE.xls]28_03_2005!$C$5&”!”&CELL(“address”,C35))

    (filename is “TEST WITH SPACE.xls”, previous sheet name is 28_03_2005.)

    Somehow I need to change PrevSheets so that the INDIRECT formula treats TEST WITH SPACE as all one string. Any suggestions?

    Jean

  10. Hi..
    Could u pls help me to get the names of all sheets in a work sheet?
    I tried Allsheets option. But could not succeed. Pls help

    JOji

  11. I was trying to use the function – AllSheets =GET.WORKBOOK(1+0*now())- to return a list of all the worksheet names in a workbook, but couldn’t get the full array (I was only getting the first value). What am I doing wrong?

    Thanks for the help.

    PS – I ended up using the code below, but I’m interested in figuring out what I did wrong.

    Sub sheetindex()
    NumSheets = Sheets.Count
    For j = 1 To NumSheets
    Cells(j, 1) = Sheets(j).Name
    Next j
    End Sub

  12. Ive tried to use that formulas but ive got an error (formula has an error – indirect is highlighted) when Im trying to insert name ThisSheet =GET.CELL(32+0*now(),indirect(“rc”,False))

    Do you know why this is happening?

  14. Dick –

    I post lots of code on my own blog, and I frequently go back and copy the code from my posts into projects I’m working on. Having to replace quotes is a royal pain, and just for my own use, I’ve started changing characters into html-friendly but author-aggravating codes (e.g., & q u o t ; for double quotes). I’m about three aggravating translations away from coding something in VBA to translate such characters for me. The characters include double and single quotes, proper spacing through nonbreaking spaces, and there are a couple more I forget until I have to fix them. When I get something done, I’ll post it.

  16. =INDIRECT(“”&PrevSheet&”!”&CELL(“address”,C35))

    Dit werkt niet bij mij
    Ik krijg telkens een foutmelding

  18. It works for me in 2019. I cleaned up some formatting in the post, so you might try it again.

Posting code? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

Your email address will not be published.