Help Needed with Dynamic CountIF

[Steve]

Hello All.

I have a sheet like this on a tab called Summary:

Month: Jan'10 (This is a drop down list showing all months)

Sales Person New Clients Sec Lead Source Minutes
SB GR Gen Pros
SB SB DB Pros
SB SB Pros
JS N/A

I have 12 additional tabs (Jan'10, Feb'10, Mar'10..etc etc etc)

What I am trying to achieve is a count of some data from the sheets on the
other tabs. This is what i have so far:

=COUNTIFS('Jan''10'!C:C,E10,'Jan''10'!M:M,G10,'Jan''10'!H:H,$D$5)

FYI D5 (at the end) refers to the drop down month field as above. You will
note that the formula above only refers to the tab 'Jan'10, what i need is
for the tab reference to be dynamic based on what the user selects in D5, so
for example, if the D5 = Feb'10 then the formula would be:

=COUNTIFS('Feb''10'!C:C,E10,'Feb''10'!M:M,G10,'Feb''10'!H:H,$D$5)

I have a good level of skill in excel but nothing with VB so any ideas would
be much appreciated.

Steve

 

[Bob Phillips]

No need for VBA

=COUNTIFS(INDIRECT("'"&D5&"'!C:C"),E10,INDIRECT("'"&D5&"'!M:M"),G10,INDIRECT("'"&D5&"'!H:H"),$D$5)

 

[Jacob Skaria]

Hi Steve

Try the below

=COUNTIFS(INDIRECT("'" & SUBSTITUTE($D$5,"'","''") & "'!C:C"),E10,
INDIRECT("'" & SUBSTITUTE($D$5,"'","''") & "'!M:M"),G10,
INDIRECT("'" & SUBSTITUTE($D$5,"'","''") & "'!H:H"),$D$5)

 

[Steve]

Some reason just returned #value ??? but Jacobs idea worked fine so thanks
anyway!

 

[Steve]

Worked a dream!! thank you very much!! - much much apprecaited