Function refering sheet index.

[Joe_Germany]

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)
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) ]
so that I can make the sheet index [ =counta(sheet(A5)!D) ] a
variable and automate the same for all sheets.


Thanks a lot... a quick responce will be of great help...

Regards
Joe

 

[Guest]

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]

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.

--
Wigihttp://www.wimgielis.be= Excel/VBA, soccer and music

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)
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) ]
so that I can make the sheet index [ =counta(sheet(A5)!D) ] a
variable and automate the same for all sheets.

Thanks a lot... a quick responce will be of great help...

Regards
Joe- Hide quoted text -

- Show quoted text -

Thats disappointing
thansk anyway, wigi..

no I have to write a vba code .... lazzy me...

regards
Joe

 

[Arvi Laanemets]

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)

 

[Harlan Grove]

normally the function will be .....
=counta('sheet1'!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)

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")),"")

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")),"")

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.