Multiple formulas including INDIRECT

[Gizmo]

Hi guys,

I start with a simple COUNTIF formula that is like this:

=COUNTIF(Germany!$B$6:$BK$6,"22.12.2004")

Then, I would like to make it more flexible, as there can be multiple
worksheets and multiple dates to request. The parts that I would like to
refer to indirectly would be country's name (f. ex. "Germany") and the
specific date (f. ex. 22.12.2004). The formula would be implemented on the
worksheet named "Totals".

Intuitively, I thought that the new formula should be more or less like the
one just below, but for some reason it's not working...

=COUNTIF(INDIRECT(B1&"!$B$6:$BK$6,Totals!"&A2))

(FYI: In the cell B1 I would put worksheets' name (f. ex. Germany) and in A2
would put the date I'm interested in (f. ex. 22.12.2004))

Any idea what I'm doing wrong?
Thanks for your help!

Gizmo

 

[JE McGimpsey]

INDIRECT returns a range, not just a string. Try:

=COUNTIF(INDIRECT(B1&"!$B$6:$BK$6"),Totals!A2)

 

[Frank Kabel]

Hi
try
=COUNTIF(INDIRECT("'" & B1 &"'!B6:BK6"),C1)
B1: country name
C1: date value

 

[Gizmo]

Thanks guys for your valuable comments!
Now I see much clearer...

Gizmo

 

[David McRitchie]

Hi Gizmo,
Certainly don't want to discourage thanking responders in a
thread or in email, but it is best not to change the subject,

It is hard to follow a thread in Google Groups when the subject
changes. It also tends to confuse -- is it the same thread or not.

And it is important in some threads to indicate which solution was
tried/worked in those that have conflicting suggestions, a change
of subject may make the thank you look like it applies to something else.

Most of the people answering will see replies to their answers,
usually in RED and by using Outlook Express. Without the quoted
material you included, one might have to switch to viewing all
messages just to tell which question was being responded to as
many responders answer more than one question in day though
there are few days that anyone surpasses Frank in number of
responses.