Find sheet name of a worksheet using relative reference

Question

I'm trying to generate 3 lists on one page that shows the A:A range of the previous 3 day's sheets using the INDIRECT formula and concatenation(eg: INDIRECT("'" & B2 & "'!A2") where B2 would equal the name of t, but I need to pull the name of the prior day's sheet (eg: 10-17) into cell B2.

I've got a working function that can find the value of a cell in another sheet by relative location, but I can't seem to get the sheet name by relative position.

Function NextSheet(rCell As Range)
    Application.Volatile
    Dim i As Integer
    i = rCell.Cells(1).Parent.Index
    NextSheet = Sheets(i + 3).Range(rCell.Address)
End Function

and another function that can return the sheet name of the sheet:

Function SheetName(rCell As Range, Optional UseAsRef As Boolean) As String
    Application.Volatile
        If UseAsRef = True Then
            SheetName = "'" & rCell.Parent.Name & "'!"
        Else
            SheetName = rCell.Parent.Name
        End If
End Function

But I can't seem to use them together (ie: NextSheet(SheetName(A2)), or SheetName(NextSheet(A2)) Is there a similar function I could use to return the sheet name by relative position?

Secondary question: Can I not call a user created function inside of another user created function like that, or are they just not set up correctly to be called inside each other?

When you use NextSheet what are you expecting as a return? Currently it will return the VALUE of a cell on a page 3 from the reference. And You cannot return the value as range object to Sheetname. — Scott Craner

SeanC SeanC · Accepted Answer · 2017-10-17T21:06:29

Part 1:
You can use the indexing that Excel has for the Sheets objects.

With no error trapping:

Sheets(ActiveSheet.Index + 1).Name
'or
Sheets(Sheets("Sheet1").Index + 1).Name

You will have to deal with the case of trying this on the last sheet (Error 9: Subscript out of range)

part 2:
Yes, calls can be nested. The return value from one will be passed into the next, so a function like foo(bar(test(5))) is valid

Find sheet name of a worksheet using relative reference

1 Answers