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 )