Hi,

I've got a workbook with multiple worksheets. A new sheet is addede
every month.
What I'd like to do is to programatticslly create an aut updating
'summary page' that lists each of the worksheet names and various info
from them e.g. ...

Worksheet Count of Column A Count of Column B Highest in
column C
Oct 05 57 63
28974.26
Nov 05 94 15
36541.44

etc..

one line for each w/sheet...

can anyone point me in the right direction??

TIA

Probably easier to do it manually...
(it's only once a month anyway)

You could use indirect function to make it easier
to copy the formulas.

=SUM(Indirect(a3&"!A:A")
where the sheetname you;d want was in a3

There's ways to make this work without you having to
type in the sheetnames...

you could put some xl4 macro's in defined names...
e.g.
tabs = get.workbooks(1)
then
in a3 = 1
in b3 = =COUNTA(INDIRECT(INDEX(tabs,,A3)&"!A:A"))

using xl4 macro's in names has drawbacks:
- macro warnings on opening the book.
- instability on copying sheets in xl97

you could replace the xl4macro with a VBA udf.




--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


wrote :

Hi,

I've got a workbook with multiple worksheets. A new sheet is addede
every month.
What I'd like to do is to programatticslly create an aut updating
'summary page' that lists each of the worksheet names and various
info from them e.g. ...

Worksheet Count of Column A Count of Column B Highest in
column C
Oct 05 57 63
28974.26
Nov 05 94 15
36541.44

etc..

one line for each w/sheet...

can anyone point me in the right direction??

TIA

I had to enter as follows: in my master sheet on Row 2
Where A2 = Oct 05, A3 = Nov 05 etc..

B2 =COUNTA(INDIRECT("'"&$A2&"'!A:A"))



C2 =COUNTA(INDIRECT("'"&$A2&"'!B:B"))


D2 =MAX(INDIRECT("'"&$A2&"'!C:C"))


and copy down


"keepITcool" wrote in message
.com...

Probably easier to do it manually...
(it's only once a month anyway)

You could use indirect function to make it easier
to copy the formulas.

=SUM(Indirect(a3&"!A:A")
where the sheetname you;d want was in a3

There's ways to make this work without you having to
type in the sheetnames...

you could put some xl4 macro's in defined names...
e.g.
tabs = get.workbooks(1)
then
in a3 = 1
in b3 = =COUNTA(INDIRECT(INDEX(tabs,,A3)&"!A:A"))

using xl4 macro's in names has drawbacks:
- macro warnings on opening the book.
- instability on copying sheets in xl97

you could replace the xl4macro with a VBA udf.




--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


wrote :

Hi,

I've got a workbook with multiple worksheets. A new sheet is addede
every month.
What I'd like to do is to programatticslly create an aut updating
'summary page' that lists each of the worksheet names and various
info from them e.g. ...

Worksheet Count of Column A Count of Column B Highest in
column C
Oct 05 57 63
28974.26
Nov 05 94 15
36541.44

etc..

one line for each w/sheet...

can anyone point me in the right direction??

TIA

yep, if the sheetnames include spaces you have to put
single quotes around them.

ALSO if they contain single quotes, you must replace
those with 2 single quotes.
as in =COUNT(INDIRECT("'"&SUBSTITUTE($A2,"'","''")&"'")& "!A:A)"


--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


JMay wrote :

I had to enter as follows: in my master sheet on Row 2
Where A2 = Oct 05, A3 = Nov 05 etc..

B2 =COUNTA(INDIRECT("'"&$A2&"'!A:A"))



C2 =COUNTA(INDIRECT("'"&$A2&"'!B:B"))


D2 =MAX(INDIRECT("'"&$A2&"'!C:C"))


and copy down