« Getting the last value
Where’s the innovation? »

Referring to Previous Sheet

2nd December 2004, 07:27 am 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

Category: Worksheet Functions, Names  |  Comment (RSS)  |  Trackback

11 Comments

  1. Frank Kabel:

    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.

    2 December 2004, 7:40 am

  2. jkpieterse:

    Hi Frank,

    Indeed. Shows how little I use Excel 2003 :-)

    2 December 2004, 7:50 am

  3. TJM:

    Sod it, must read whole article before playing with it…

    Excel XP - now restarting :-)

    3 December 2004, 2:18 am

  4. Ross:

    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.

    14 December 2004, 9:12 am

  5. jkpieterse:

    Wow Dick, this site is really getting popular now!!! (see comment # 5)

    13 February 2005, 11:46 pm

  6. Dick:

    What was comment #5 is gone. Trackback spam. Thanks Jan Karel.

    14 February 2005, 10:37 am

  7. jaf:

    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

    11 April 2005, 2:56 am

  8. jkpieterse:

    You need to add single quotes at exactly the right places:

    =INDIRECT(”‘”&PrevSheet&”‘!”&CELL(”address”,C35))

    11 April 2005, 8:27 am

  9. jaf:

    Thanks jkpieterse - that’s fixed what I needed.

    11 April 2005, 10:18 am

  10. Joji:

    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

    9 June 2005, 8:02 am

  11. Brian:

    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

    13 April 2007, 10:55 am

Certain comments are subject to moderation and may not appear immediately. You can use HTML tags in your comment. If you include a greater-than or less-than sign or anything else that could be interpreted as HTML, your comment won't look nice. You need to escape those characters. To post VBA code in your comment, use [VB] tags, like this: [VB]Code goes here[/VB].

Leave a comment