Tab Reference

[tjh]

Is it possible to use an excel formula to reference the name of another
worksheet in the same file, without knowing the name of that worksheet. Such
as if you are on the first worksheet, and you want to list the names of all
the worksheets following the current worksheet . If there are five worksheet
and you would like to list the names of all the worksheet in cells A1 - A5
respectively.

 

[Gary''s Student]

This UDF wil return the name of the ith sheet:

Function sheetname(i As Integer) As String
sheetname = Worksheets(i).Name
End Function

So =sheetname(1) will return the name of the first sheet, etc.

 

[CLR]

First save your file....

Then, if you create a RANGENAME on each sheet, that is unique to that sheet,
no matter what the sheet name is, then you can put this formula in A1 of
Sheet1 and copy down, but must replace the rangename in each formula to
return the sheetname that contains the rangename..........

=MID(CELL("filename",RANGENAME1),FIND("]",CELL("filename",RANGENAME1),1)+1,99)

This in A2, etc
=MID(CELL("filename",RANGENAME2),FIND("]",CELL("filename",RANGENAME2),1)+1,99)


hth
Vaya con Dios,
Chuck, CABGx3

 

[Herbert Seidenberg]

If you have a lot of sheets and
are positively allergic to VBA, you can
automate Chuck's excellent method thus:
Right-click on any tab > Select all sheets
(Color all tabs to make the new selection obvious)
Enter Chuck's modified formula into A2:
=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,42)
Now all worksheets will show their tab name at A2.
With all tabs still selected, enter a 1 into A3.
Into A4 enter this formula:
=SUM('*'!A3)
Note that Excel expands this formula. Sample:
=SUM(SheetA:SheetM!A3,SheetO:SheetZ!A3)
Find > !A3* > Replace > !A3) > Replace All
Except for the Summary sheet, the first sheet,
the sheets will now have sequential numbers at A4.
(Page of Pages application?)
Change A4 on the Summary sheet to 0.
Select all sheets and enter at A1
="Page"&A4
Select A1:A2 and
Insert > Name > Create > Top Row
On the Summary sheet at B1 enter and copy down:
=INDIRECT("Page"&ROW())