Return Sheetname for different Worksheet

[scarlett3]

I know that the following function returns the Sheetname for the current Worksheet:

=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,256)

But I'd like to return the Sheetname for the Worksheet that is one tab to the right. Is this possible?

I add new Worksheets with each new day with data relating to that particular day. I also have a 'Master' Worksheet and I'd like to be able to refer to the latest Worksheet, i.e. one tab to the right, within a formula without having to manually enter the name of that Worksheet.

Thanks

 

[mse330]

If you are okay with VBA, the below two UDFs can acheive what you're looking for

Function NextSheetName() As String

Application.Volatile
NextSheetName = Sheets(ActiveSheet.Index + 1).Name

End Function

Function LastSheetName() As String

Application.Volatile
LastSheetName = Sheets(ActiveWorkbook.Sheets.Count).Name

End Function

 

[scarlett3]

Thanks mse330

 

[mse330]

Glad I could help. You are welcome