workbook indexing
 

I want to create first sheet of workbook as index sheet that contains all
sheet names of that workbook.How?

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?

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?

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?

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 )