Formula to find workbook tab number

**dellschipper** · 10-05-2011, 11:33 AM

I am looking for a formula to place within each worksheet to identify the sequence number of that worksheet.

For example, if I have a workbook containing four worksheets named "Start", "Alpha", "Beta", and "End" I want a formula within the "Alpha" worksheet that contains a workbook tab number of 2 since it is the second worksheet within the workbook.

My preference is not to use VBA or a macro but I will accept any help.

Thanks, Dell.

**NBVC** · 10-05-2011, 12:51 PM

Make a list of all the worksheets in a column of any sheet.. then name that range by typing in the Name box to the left of the formula bar... e.g. TabNames.

Then try:

=MATCH(MID(CELL("filename",$A$1),FIND("]",CELL("filename",$A$1))+1,255),TabNames,0)

**Lucky:)** · 10-05-2011, 01:55 PM

what is the list of sheet is variable?

**NBVC** · 10-05-2011, 02:00 PM

Insert the sheet name into the list at appropriate position.

Please continue correspondence in your own thread.

**dellschipper** · 10-05-2011, 02:25 PM

Well, the number of worksheets is variable and my intent was to produce the table rather than start with the table. I was planning on using a SUMIF formula going from "Start:End" and matching the workbook tab number with a sequential list to populate the table.

**NBVC** · 10-05-2011, 02:37 PM

I think if you will need VBA udf then...

Perhaps...

Function Sheetnum()
    For i = 1 To sheets.Count
        If ActiveSheet.Name = sheets(i).Name Then
        Sheetnum = i
        End If
    Next i
End Function

in any sheet type =Sheetnum() to get active sheetnumber