Reference next or previous sheet in a formula if the sheet number/name is a varible/variant

Question

How do I reference next or previous sheet in a formula if the sheet number/name is a varible/variant.

I have seen ways to do this with vba, is there a way to do this with a formula or function

Solution 1

1. In Excel. Enter an equals sign "=" in any cell. This will put you into interactive mode.

2. Navigate to your other sheet and click the cell you want to reference. Then hit enter.

3. Go back to the previous sheet and edit the cell you started in and you should see the formula to use.

To reference the next/prevoius sheet (See example here). You'll need some VB code I think (something along the lines of):

Function PrevSheet(RCell As Range)
    Dim xIndex As Long
    Application.Volatile
    xIndex = RCell.Worksheet.Index
    If xIndex > 1 Then _
        PrevSheet = Worksheets(xIndex - 1).Range(RCell.Address)
End Function

then use

=PrevSheet(A1)

Solution 2

Here is another way to list out all the sheet names with regular Excel formula:

1. Cell A1: =CELL("filename",1:1048576)

2. Cell B1: =MID(A1,FIND("[",A1)+1,FIND("]",A1)-FIND("[",A1)-1)

3. Go to Formula > Name Manager > New, like the pic below, enter SheetNames in the Name field and the formula below in the Refers to field:

   =GET.WORKBOOK(1,Sheet29!$B$1)&T(NOW())

   Please note that I setup this on Sheet29. You should change that to the sheet you are using.

4. From cell A2, fill out the formula below and drag down to list all the sheet names:

   =INDEX(MID(SheetNames,FIND("]",SheetNames)+1,255),ROWS(A$1:A1))

In my example, I don't have Sheet28 and also the sheet name is sorted by the order from left to right. Move around and check the result.

Comments

[Dan]

Thanks, however this only inputs the sheet name, the sheetname will be unknown but will always be the sheet to the right. Is there a way I can reference this?

[Peter]

See extendoffice.com/documents/excel/… "=PrevSheet(A1)"

[Dan]

Thanks I see how this would work, However, this is reliant on the sheet numbers aligning with the amount of sheets. I have a macro running that deletes various sheets depending on different inputs upon completion of the macro I will only have two sheets, one will always be sheet1 the other could be anything from sheet2 to sheet30