Previous Sheet

This is what you can use:

Please Login or Register  to view this content.

Then, for real use, you can valorize a variable instead of the Debug.Print and add a MsgBox if there is no previous sheet.

Sorry, no idea how this can be done with a simple formula; for sure needs at least an UDF.
Try googling "previous sheet formula excel".

Add a named range, via Name Manager, called Sheetnames
=REPLACE(GET.WORKBOOK(1),1,FIND("]",GET.WORKBOOK(1)),"")


The depending on excel version either of these formula.

=IFERROR(INDEX(SHEETNAMES,1,MATCH(TEXTAFTER(CELL("Filename",$A$1),"]"), SHEETNAMES,0)-1),"")
=IFERROR(INDEX(SHEETNAMES,1,MATCH(MID(CELL("Filename",$A$1),FIND("]",CELL("Filename",$A$1))+1,LEN(CELL("Filename",$A$1))), SHEETNAMES,0)-1),"")