Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
workbook indexing
I want to create first sheet of workbook as index sheet that contains all
sheet names of that workbook.How? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
workbook indexing
What are you doing, writing a book <grin
See http://www.mvps.org/dmcritchie/excel/buildtoc.htm or something shorter in http://www.mvps.org/dmcritchie/excel/buildtoc2.htm --- HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/dmcritchie/excel/search.htm "TUNGANA KURMA RAJU" wrote in message ... I want to create first sheet of workbook as index sheet that contains all sheet names of that workbook.How? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
workbook indexing
Hi
This UDF allows it - copy it into any workbook module. Public Function TabI(TabIndex As Integer, Optional MyTime As Date) As String TabI = Sheets(TabIndex).Name End Function Now, on first sheet, p.e. into cell A2 enter the formula =IF(ISERROR(TABI(ROW(),NOW())),"",TABI(ROW()) and copy down. In A2 you see the name of your 2nd sheet, in A3 the name of 3rd one, etc. -- Arvi Laanemets ( My real mail address: arvil<attarkon.ee ) "TUNGANA KURMA RAJU" wrote in message ... I want to create first sheet of workbook as index sheet that contains all sheet names of that workbook.How? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
workbook indexing
Thanks alot,it worked perfectly.But I don't understand the logic of the
formula. "Arvi Laanemets" wrote: Hi This UDF allows it - copy it into any workbook module. Public Function TabI(TabIndex As Integer, Optional MyTime As Date) As String TabI = Sheets(TabIndex).Name End Function Now, on first sheet, p.e. into cell A2 enter the formula =IF(ISERROR(TABI(ROW(),NOW())),"",TABI(ROW()) and copy down. In A2 you see the name of your 2nd sheet, in A3 the name of 3rd one, etc. -- Arvi Laanemets ( My real mail address: arvil<attarkon.ee ) "TUNGANA KURMA RAJU" wrote in message ... I want to create first sheet of workbook as index sheet that contains all sheet names of that workbook.How? |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
workbook indexing
Hi
"TUNGANA KURMA RAJU" wrote in message ... Thanks alot,it worked perfectly.But I don't understand the logic of the formula. Public Function TabI(TabIndex As Integer, Optional MyTime As Date) As String The 1st parameter says, the name of which tab you want the function to return. The 2nd parameter is to make the function volatile. Whenever something is calculated on sheet, when MyTime changes, the function is recalculated. Otherwise you have to force the recalculation manually. TabI = Sheets(TabIndex).Name The name of TabIndex'th tab is saved to variable TabI (In Excel, the function returns the value, saved in variable with same name as function itself). End Function Now, on first sheet, p.e. into cell A2 enter the formula =IF(ISERROR(TABI(ROW(),NOW())),"",TABI(ROW()) The formula TABI(ROW()) invokes the UDF with parameter 1, 2, etc, depending on the row the formula resides. As result, the name of according tab is returned. In case TABI() returns an error (no tab with such index exists, p.e. your workbook has only 3 tabs, but the formula is looking for 4th or 5th one), error trapping is implemented. When UDF returns error, an empty string is returned by formula. In formula the function TABI() is used twice. I used NOW() as optional parameter (the value of NOW() changes every second), to make the formula volatile. (Of course I could make the function itself volatile, but current solution is more flexible.) And it's enough to use optional parameter only once in formula. -- Arvi Laanemets ( My real mail address: arvil<attarkon.ee ) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
HOW DO I SUM TWO CELLS FROM ONE WORKBOOK TO ANOTHER WORKBOOK? | Excel Worksheet Functions | |||
Auto updating a workbook with data from another workbook | Excel Discussion (Misc queries) | |||
Linking a cell to another workbook cell based on a variable name | Excel Discussion (Misc queries) | |||
Unprotect Workbook | Excel Discussion (Misc queries) | |||
Stubborn toolbars in Excel | Excel Discussion (Misc queries) |