|
|
|
|
|
|
Excel
|
Get sheet index number from cell reference (Function)
|
|
Ease of Use
|
Easy
|
Version tested with
|
2000, 2002, 2003
|
Submitted by:
|
Zack Barresse
|
Description:
|
This function will return a sheet index number for a given reference. If no reference is given, the assumption is for the sheet the formula was entered on.
|
Discussion:
|
The need for the sheets index number may arise, this is an easy way to tell. This would be helpful if you needed to always index the next sheet no matter what sheet it was. Then you could use, "=SheetNum()+1". This may be helpful for indexing chronological sheets, possibly monthly tabs.
|
Code:
|
instructions for use
|
Option Explicit
Function SheetNum(Optional celRef As Range) As Long
With Application.Caller.Parent
If celRef Is Nothing Then
SheetNum = .Index
Else
SheetNum = celRef.Parent.Index
End If
End With
End Function
|
How to use:
|
- Copy above code.
- In Excel press Alt + F11 to enter the VBE.
- Press Ctrl + R to show the Project Explorer.
- Right-click desired file on left (in bold).
- Choose Insert -> Module.
- Paste code into the right pane.
- Press Alt + Q to close the VBE.
- Save workbook before any other changes.
|
Test the code:
|
- Install the code.
- Save workbook.
- In a blank cell, enter "=SheetNum()"
- Enter a reference inside the parentheses (ref. can be a cell on another sheet).
|
Sample File:
|
SheetNumEx.zip 6.75KB
|
Approved by mdmackillop
|
This entry has been viewed 201 times.
|
|