Formula to return Sheet Number

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).
you may use a udf

Code:
Function SheetPosition() As Integer
'http://www.cpearson.com/excel/sheetref.htm

    Application.Volatile True
    SheetPosition = Application.Caller.Parent.Index

End Function
 
Upvote 0
There is a VB solution available... a UDF (user defined function), but it depends on what you mean by the "sheet number". There are two numbers associated with a worksheet... the number indicating the position of its tab within the list of sheet tabs displayed at the bottom of the grid (this is affected, of course, by moving the worksheet's tab manually to a new position) or the "fixed" number associated with the worksheet's Code Name (this is the number that theoretically "stays" with the sheet even when you rename the sheet). With respect to this latter number... unfortunately, it is not as fixed a number as one usually thinks... it can be renumbered or even have the number completely removed and the "Sheet" part can be changed as well. I will, for the purposes of the code below, assume this "fixed" number is never changed by you or your user.

Code:
'Position within tab order
Function OrderNumber() As Long
  Application.Volatile True
  OrderNumber = Application.Caller.Parent.Index
End Function
Code:
' Fixed Code Name number
Function CodeNumber() As Long
  CodeNumber = Mid(Application.Caller.Parent.CodeName, 6)
End Function
If you are new to UDF's, you can install them quite easily. Press ALT+F11 from any worksheet to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy either or both (your choice as to what you want) of the above code(s) into the code window that opened up. That's it... you are done. Go back to any worksheet and use the name of the function in a formula just like you would any other built-in Excel function. For example...

=OrderNumber()

or

=CodeNumber()
 
Upvote 0
You could use an XLM4 macro function to return this which doesn't involve use of VBA:

1. Create a new name and call it SheetNum.
2. In the refers to box type:

=GET.DOCUMENT(87)

and click Add.
3. Back in the sheet in a cell type:

=SheetNum

which will return the index number of the sheet.
 
Upvote 0
Thanks mancubus, your function works great!

Is there a similar function I can use (VBA is ok) to get the total number of sheets in a workbook?

Thanks!
 
Upvote 0
Is there a similar function I can use (VBA is ok) to get the total number of sheets in a workbook?
Here is a VB UDF that will do it...

Code:
Function SheetCount()
  SheetCount = ThisWorkbook.Sheets.Count
End Function
 
Upvote 0
You could use an XLM4 macro function to return this which doesn't involve use of VBA:

1. Create a new name and call it SheetNum.
2. In the refers to box type:

=GET.DOCUMENT(87)

and click Add.
3. Back in the sheet in a cell type:

=SheetNum

which will return the index number of the sheet.
Cool!

Never saw that one before.

I was thinking of using GET.WORKBOOK(1) which returns an array of all the sheet names and then using MATCH to get the sheet num.

There's a lot of good stuff to be gleaned from those "old" macro functions!
 
Upvote 0
Cool!

Never saw that one before.
One possible problem though... the displayed result from Richard's Defined Name method is not Volatile so if the user moves the sheet's position around... it appears the formula will not update to show its new position until it is re-entered. Just to point out... the functions that mancubus and I posted (OrderNumber for me, SheetPosition for him) are Volatile and update automatically.
 
Upvote 0
One possible problem though... the displayed result from Richard's Defined Name method is not Volatile so if the user moves the sheet's position around... it appears the formula will not update to show its new position until it is re-entered. Just to point out... the functions that mancubus and I posted (OrderNumber for me, SheetPosition for him) are Volatile and update automatically.
OK, then we can make the function volatile like this:

=GET.DOCUMENT(87)&T(NOW())

Seems to take care of it.
 
Upvote 0

Forum statistics

Threads
1,213,561
Messages
6,114,317
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top