One way:

Public Function SheetNum(Optional rng As Range) As Variant
Application.Volatile
If rng Is Nothing Then
If TypeName(Application.Caller) = "Range" Then
Set rng = Application.Caller.Cells
Else
SheetNum = CVErr(xlErrRef)
Exit Function
End If
End If
SheetNum = rng.Parent.Index
End Function

usage:

=SheetNum()

or

=SheetNum(MySheet!A1)


note that this will not automatically update when a sheet is moved
within a workbook - Application.Volatile will cause it to update the
next time a calculation is made.


In article ,
"Brandon" wrote:

I'm looking for a way to create a function to return the current sheet youre
working in. Not its name (Ive found that function), but the number of the
sheet. Example: if you have a total of 8 sheets, and youre working on the
3rd sheet, Id like to return the number 3 in a certain cell on the third
sheet, 4 on the fourth sheet, 5 on the fifth sheet, etc., and have those
numbers update if sheets are added or deleted. Is that possible?