Function refering sheet index.
Hi
Copy the UDF into your workbooks module:
---
Public Function TabI(TabIndex As Integer, Optional parVolatile As Date) As
String
TabI = Sheets(TabIndex).Name
End Function
---
Now, p.e into cell A1 on some sheet enter the formula
=IF(ISERROR(TABI(ROW(),NOW())),"",TABI(ROW()))
, and copy down for as much rows as you need - you get a list of sheet names
in your workbook (in tab order).
(The optional parameter in formula allows to turn the formula volatile at
will)
--
Arvi Laanemets
( My real mail address: arvi.laanemets<attarkon.ee )
"Joe_Germany" wrote in message
ups.com...
Dear all,
I have some 100+ sheets in a file.
in the first sheet I want get a consolidated information. say count
the entries in column-D.
how can I write a funcion indipendant of the sheet name.
for ex,
normally the function will be .....
=counta('sheet1'!D:D)
here we have to maually change the name for each sheets.
I would like to know if the sheet index can be used in this function.
[ =counta(sheet(2)!D:D) ]
so that I can make the sheet index [ =counta(sheet(A5)!D:D) ] a
variable and automate the same for all sheets.
Thanks a lot... a quick responce will be of great help...
Regards
Joe
|