I have a workbook that has 1 worksheet for everyday of current month. So for
July, it has worksheets 1, 2, 3,....31. The name of worksheets correspond to
the date, i.e., 1, 2, 3...31. I have a summary worksheet to do calculation,
in column 1 I put in the dates 1, 2, 3...31, formatted to display "dd". Then
in other columns I put in the formula to do the calculation, sample:
1 =COUNTIF('1'!$E$4:$E$600,"No") =COUNTIF('1'!A4:A600,"0")
2 =COUNTIF('2'!$E$4:$E$600,"No") =COUNTIF('2'!A4:A600,"0")
3 =COUNTIF('3!$E$4:$E$600,"No") =COUNTIF('4'!A4:A600,"0")
4 =COUNTIF('4'!$E$4:$E$600,"No") =COUNTIF('4'!A4:A600,"0")

My question is how do I make these formulae dynamic, so that the worksheet
name can be referred to the cell in column A, instead of manually put in 1,
2, 3, 4...? Something like: =countif('A2'!$E$4:$E$600,"No").

Try this...

A1 = 7/1/2009 formatted to display as "dd"

=COUNTIF(INDIRECT("'"&DAY(A1)&"'!E4:E600"),"No")

Copy down as needed.

--
Biff
Microsoft Excel MVP


"Sing Chung" <Sing wrote in message
...
I have a workbook that has 1 worksheet for everyday of current month. So
for
July, it has worksheets 1, 2, 3,....31. The name of worksheets correspond
to
the date, i.e., 1, 2, 3...31. I have a summary worksheet to do
calculation,
in column 1 I put in the dates 1, 2, 3...31, formatted to display "dd".
Then
in other columns I put in the formula to do the calculation, sample:
1 =COUNTIF('1'!$E$4:$E$600,"No") =COUNTIF('1'!A4:A600,"0")
2 =COUNTIF('2'!$E$4:$E$600,"No") =COUNTIF('2'!A4:A600,"0")
3 =COUNTIF('3!$E$4:$E$600,"No") =COUNTIF('4'!A4:A600,"0")
4 =COUNTIF('4'!$E$4:$E$600,"No") =COUNTIF('4'!A4:A600,"0")

My question is how do I make these formulae dynamic, so that the worksheet
name can be referred to the cell in column A, instead of manually put in
1,
2, 3, 4...? Something like: =countif('A2'!$E$4:$E$600,"No").

Wonderful!

"T. Valko" wrote:

Try this...

A1 = 7/1/2009 formatted to display as "dd"

=COUNTIF(INDIRECT("'"&DAY(A1)&"'!E4:E600"),"No")

Copy down as needed.

--
Biff
Microsoft Excel MVP


"Sing Chung" <Sing wrote in message
...
I have a workbook that has 1 worksheet for everyday of current month. So
for
July, it has worksheets 1, 2, 3,....31. The name of worksheets correspond
to
the date, i.e., 1, 2, 3...31. I have a summary worksheet to do
calculation,
in column 1 I put in the dates 1, 2, 3...31, formatted to display "dd".
Then
in other columns I put in the formula to do the calculation, sample:
1 =COUNTIF('1'!$E$4:$E$600,"No") =COUNTIF('1'!A4:A600,"0")
2 =COUNTIF('2'!$E$4:$E$600,"No") =COUNTIF('2'!A4:A600,"0")
3 =COUNTIF('3!$E$4:$E$600,"No") =COUNTIF('4'!A4:A600,"0")
4 =COUNTIF('4'!$E$4:$E$600,"No") =COUNTIF('4'!A4:A600,"0")

My question is how do I make these formulae dynamic, so that the worksheet
name can be referred to the cell in column A, instead of manually put in
1,
2, 3, 4...? Something like: =countif('A2'!$E$4:$E$600,"No").

You're welcome!

--
Biff
Microsoft Excel MVP


"Sing Chung" wrote in message
...
Wonderful!

"T. Valko" wrote:

Try this...

A1 = 7/1/2009 formatted to display as "dd"

=COUNTIF(INDIRECT("'"&DAY(A1)&"'!E4:E600"),"No")

Copy down as needed.

--
Biff
Microsoft Excel MVP


"Sing Chung" <Sing wrote in message
...
I have a workbook that has 1 worksheet for everyday of current month. So
for
July, it has worksheets 1, 2, 3,....31. The name of worksheets
correspond
to
the date, i.e., 1, 2, 3...31. I have a summary worksheet to do
calculation,
in column 1 I put in the dates 1, 2, 3...31, formatted to display "dd".
Then
in other columns I put in the formula to do the calculation, sample:
1 =COUNTIF('1'!$E$4:$E$600,"No")
=COUNTIF('1'!A4:A600,"0")
2 =COUNTIF('2'!$E$4:$E$600,"No")
=COUNTIF('2'!A4:A600,"0")
3 =COUNTIF('3!$E$4:$E$600,"No")
=COUNTIF('4'!A4:A600,"0")
4 =COUNTIF('4'!$E$4:$E$600,"No")
=COUNTIF('4'!A4:A600,"0")

My question is how do I make these formulae dynamic, so that the
worksheet
name can be referred to the cell in column A, instead of manually put
in
1,
2, 3, 4...? Something like: =countif('A2'!$E$4:$E$600,"No").