workbook indexing

  • Thread starter TUNGANA KURMA RAJU
  • Start date
T

TUNGANA KURMA RAJU

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

Arvi Laanemets

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.
 
T

TUNGANA KURMA RAJU

Thanks alot,it worked perfectly.But I don't understand the logic of the
formula.
 
A

Arvi Laanemets

Hi


TUNGANA KURMA RAJU said:
Thanks alot,it worked perfectly.But I don't understand the logic of the
formula.
Click to expand...

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.


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).


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.
 
Top