using worksheet name in a formula but not current worksheet

S

Sing Chung

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").
 
T

T. Valko

Try this...

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

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

Copy down as needed.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Finding the weighted average of text that have been given values 1
Need some PWA help 0
using CountIF function or Array? 3
Varying cell ranges in formulas according to number of cells with certain value 1
Filtering data 2
Possible to refer to a sheet's object name ? 1
Vlookup and return sheet name also 2
COUNTIF: everything in a row larger than zero over multiple worksh 1

Top