Function refering sheet index.
Joe_Germany
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
Wigi
You could consider writing your own new function, a user-defined function
(UDF) to do it.
Alternatively, you could setup the list of all sheet names and work with the
INDIRCT function.
--
Wigi
http://www.wimgielis.be = Excel/VBA, soccer and music
Joe_Germany
> You can't do that with today's Excel functions.
>
> You could consider writing your own new function, a user-defined function
> (UDF) to do it.
>
> Alternatively, you could setup the list of all sheet names and work with the
> INDIRCT function.
>
> --
>
>
>
> "Joe_Germany" wrote:
> > 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
>
> - Show quoted text -
Thats disappointing :(
thansk anyway, wigi..
no I have to write a vba code .... lazzy me...
regards
Joe
Arvi Laanemets
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<at>tarkon.ee )
"Joe_Germany" <Joe.Varg...@gmail.com> wrote in message
news:1185536166....@o61g2000hsh.googlegroups.com...
Harlan Grove
...
>normally the function will be .....
>=counta('sheet1'!D:D)
>here we have to maually change the name for each sheets.
This formula would be in a worksheet other than Sheet1? If not, i.e., it'd
be in Sheet1, just use
=COUNTA(D:D)
OTOH, if you were putting together a summary worksheet with these results
from all other worksheets, you could do this without VBA but with another
worksheet, some defined names and some formulas.
Insert a new worksheet and name it ' ' (a single space, without the single
quotes). Define the name ' '!_WSLST (that's a worksheet-level name, so
include the ' '! before _WSLST) referring to the formula
=SUBSTITUTE(GET.WORKBOOK(1),"["&GET.DOCUMENT(88)&"]","")
Then select ' '!A1:IV1 and enter the ARRAY formula
=_WSLST
Then name that range WSLST (that's a workbook-level name, so no worksheet
name preceding it). The first named range uses XLM functions to return an
array of worksheet names. The array formula puts them into a worksheet range
(this prevents problems using references to XLM functions in older Excel
versions). That range may be referenced using the second name.
If your summary worksheet came first and ' ' second, and your detail
worksheets started with the third and went through the 102nd worksheets, you
could enter the following formula in cell B2 of the summary worksheet.
B2:
=IF(ROWS(B$2:B2)+2<=COUNTIF(WSLST,"*"),
COUNTA(INDIRECT("'"&INDEX(WSLST,ROWS(B$2:B2)+2)&"'!D:D")),"")
This should return the number of entries in column D of the first detail
worksheet which is the third worksheet in the workbook/file. Fill this down
into B3, and it becomes
B3:
=IF(ROWS(B$2:B3)+2<=COUNTIF(WSLST,"*"),
COUNTA(INDIRECT("'"&INDEX(WSLST,ROWS(B$2:B3)+2)&"'!D:D")),"")
which should return the number of entries in column D of the second detail
worksheet which is the fourth worksheet in the workbook/file. Fill this down
into B4:B101 to get the numbers of entries in columns D of the other detail
worksheets.