Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Tab Reference
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Tab Reference
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. -- Gary''s Student - gsnu200761 "tjh" wrote: 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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Tab Reference
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 "tjh" wrote: 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. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Tab Reference
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()) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Nesting a sheet name reference within a cell reference??? | Excel Discussion (Misc queries) | |||
Changing sheet reference to cell reference | Excel Worksheet Functions | |||
Formulas that reference cells that reference another cell | Excel Discussion (Misc queries) | |||
Row reference increment but preserve column reference | Excel Worksheet Functions | |||
Macro to Reference Column Next to Current Reference | Excel Discussion (Misc queries) |